SELECT foo, MIN(bar) AS bar

FROM tbl

GROUP BY foo



To return the highest value, and order top to bottom by that value:

FROM tbl

GROUP BY foo

ORDER BY Count DESC;



Ditto for AVG(), COUNT() etc. It is easily extended for multiple grouping column expressions.

);

INSERT INTO packageItemTax VALUES

(1,1,'GST',7.00),

(2,1,'HST',10.00);



The query ...

GROUP BY i.packageItemID;



returns ...

+---------------+-----------------+------------------+-------+--------+

|             1 | Delta Hotel     |           100.00 | 34.00 | 234.00 |

+---------------+-----------------+------------------+-------+--------+



With three child tables, the sums are tripled. Why? Because the query aggregates across each join.How then to get the correct results? With correlated subqueries:

|             1 |   100.00 |    117.00 |  17.00 |

+---------------+----------+-----------+--------+


If subqueries are unavailable or too slow, replace them with temp tables.



and you wish to list ranks by group omitting the leading rank in each group. The simplest query for group leaders is ...

|    3 |    1 |

|    4 |   11 |

+------+------+



The simplest way to get a result that omits these is an exclusion join from the ranks table to the above result:

|    4 |   12 |

|    4 |   13 |

+------+------+




To get an overview of the values of c2 for each value of c1:

  c1, 

  GROUP_CONCAT(c2 ORDER BY c2) AS 'C2 values'

FROM table

GROUP BY c1;



To retrieve a list of c1 values for which there exist specific values in another column c2, you need an IN clause specifying the c2 values and a HAVING clause specifying the required number of items in the list ...

This is easy to generalise to multiple column expressions, and a HAVING clause specifying any number of items from the IN list.

To list c1 values that have exactly one instance of each c2 value, add DISTINCT to the count criterion:

  Percentage float(4,1) NOT NULL DEFAULT '0.0'

);



The query needs to aggregate language counts by country twice: once for all language counts by country, and once again to identify countries with the highest number of languages:

+--------------+------------+-------------+-----+



In addition, one of the nested subqueries is buried in a HAVING clause. This is fine with small tables, but if the table being aggregated is very large and the aggregation is complex, performance may be unsatisfactory. Substituting a temporary table for the double nesting can improve performance in two ways:

• the aggregation needs to be done just once
• we can use an exclusion join, which is usually faster than a HAVING clause, to find countries with the maximum counts:

| South Africa |   40377000 | Thabo Mbeki |   4 |

+--------------+------------+-------------+-----+

DROP TABLE top;




You notice that we haven't actually used a TEMPORARY table? Indeed we haven't, because of the MySQL limitation that temporary tables cannot be referenced multiple times in a query. Until that's lifted, we get almost as much speed improvement from using a MEMORY table as a temporary table.

One solution is to use derived tables. Assuming ...

CREATE TABLE users (id INT,companyid INT);

CREATE TABLE actions (id INT, userid INT, date DATE);




then...

• Join companies & users once to establish a derived company-user table.
• Join them a second time, this time aggregating on users.id to generate user counts per company.
• Join the first derived table to the actions table, aggregating on actions.id to report actions per user per company:

Here is the SQL:

  GROUP BY u3.id

) AS ua ON ua.uid=cu1.uid;




Even one level of recursion can induce a mild trance. Escape the trance by taking the problem one step at a time. First write the query that finds the authors with multiple books. Then join an outer query to that on authorid, and have the outer query select bookid:



where thistime is the TIME column and periodMinutes is the period length in minutes. So to group by 15-min periods, write ...



The same logic works for months ...



It could be made to work for weeks with a function that maps the results of WEEK() to the range 1...52.

When the desired grouping period is a value returned by a MySQL date-time function, matters become simpler: just group by the desired value. Thus to group by weeks, write ..

If there is no MySQL date-time function that returns the desired grouping period, you will need to write your own stored function.

You can also Group By an expression like



modifying the denominator to suit.

+-----------+------+------+------+------+------+------+------+------+




|    31.00 |   40.00 |      0.40 |   40 |

+----------+---------+-----------+------+



The first problem is to work out how commission ranges map to sales totals to determine base amounts for calculation of each part-commission. We assume the ranges are inclusive, ie a range that starts at 1 euro is meant to include that first euro:

• if amt < comstart, base amount = 0
• if amt <= commend, base amount = amt-comstart+1
• if amt > commend, base amount = commend - comstart+1

This is a nested IF():

IF(s.amt<c.comstart,0,IF(s.amt<=c.commend,s.amt-c.comstart,c.commend-c.comstart))



The second problem is how to apply every commission range row to every employee sales sum. That's a CROSS JOIN between aggregated sales and commissions:

+------------+------+----------+---------+-----------+------+



Now check how the formula applies on every commission band for every sales total:

+------------+------+----------+---------+-------+--------+--------+



Finally, SUM the formula results to aggregate commissions on aggregated sales:

|          2 |   36 | 8.400000 |

+------------+------+----------+






But is it accurate? No, because it displays only the first c.pid value it happens to find. For further discussion see Within-group aggregates.

SELECT col

FROM foo

GROUP BY col




+------+----------+-------+



Your first thought may be to GROUP BY item, but that is not guaranteed to return the correct supplier value for each minimum item price. Grouping by both item and supplier will return more information than you want. Nor can you write WHERE price=MIN(...) because the query engine will evaluate the WHERE clause before it knows the MIN value.

This is the problem of aggregating within aggregates. It is sometimes called the 'groupwise aggregates' problem, but the term 'groupwise' is ambiguous at best, so we think better names for it are subaggregates, inner aggregates, or within-group aggregates.

It's easy to show that the within-group aggregates problem is a form of the problem of returning values from non-grouping columns in an aggregate query. Suppose you write ...

FROM products

GROUP BY item;



Will this reliably return the correct supplier per item? No. Unless there is exactly one supplier per item, the supplier value returned will be arbitrary. To retrieve the correct supplier for each item, you need more logic.

The simplest and often best-performing solution to the within-aggregates problem is an outer self exclusion join...

WHERE p2.item IS NULL;



...because in the resultset built by joining on left item = right item and left price > right price, the left-sided rows for which there is no greater right-sided price are precisely the per-item rows with the smallest prices.

You can also accomplish this by building a table of aggregated minimum prices. Before MySQL 4.1, it has to be a temporary table:

  FROM products 

  GROUP BY item;



to which you then join the products table:

From MySQL 4.1 on, the temporary table can be a correlated subquery. This is the most intuitively obvious syntax for the problem. Often it's also the slowest solution:

  SELECT MIN(p2.price)

  FROM products AS p2

  WHERE p1.item = p2.item

);



Another solution, sometimes the fastest of all, is to move the aggregating subquery from the WHERE clause to the FROM clause:

Finally, here is a rather different approach which can find both within-group minima and within-group maxima in a single query. The idea is to aggregate the concatenated within-group grouped column value and the within-group grouping column name in a single string, then use SUBSTR() to break them apart in the result:

+------+-------------+----------+-------------+----------+



Try all solutions to find which is fastest for your version of the problem.

To find the top or bottom N per group, you might think the LIMIT clause would work, but LIMIT is limited in subqueries. See Within-group quotas.

+------+------------+--------+--------------+------+-------+



For the above dataset, the result which correctly matches wages and tips would be:

+------+------------+------+------+-------+------+--------+------+------------+



Why is this different from an ordinary within-groups aggregate? The correct wage rate for a tips row is not the wages row for that waiter with the latest date; it is the wages row having the latest date before the date in the given tips row.

One way to proceed is to build a temporary table from a join of wages to tips on waiter and wages.start < tips.date, then exclusion-join that result to itself to remove all rows except those with the latest wage rate per tips row. A two-step:

+------+------------+------+------+-------+------+--------+------+------------+

DROP TABLE tmp;



That's fine, but can we skip the temp table? Yes—by adding the condition wages.start <= tips.date to each side of the exclusion join:



Much simpler, and it gives the same result as the two-step.

If the groups are fairly small, this can be done efficiently with a self-join and counts. For example the following table (based on a tip by Rudy Limeback) has three small data groups:

( 3, '2007-7-03' );



The first two rows per ID are the rows which, for a given ID, have two or fewer rows with earlier dates. If we use an inequality join with the COUNT(*) function to find the earlier rows per ID ...

+------+------------+---------+



... then we get our result immediately by removing rows where the 'earlier' count exceeds 2:

|    3 | 2007-07-02 |       2 |

+------+------------+---------+



This works beautifully with smallish aggregates. But the query algorithm compares every within-group row to every other within-group row. As the size N of a group increases, execution time increases by N*N. If the query takes one minute for groups of 1,000, it will take 16 minutes for groups of 4,000, and more than four hours for groups for 16,000. The solution does not scale.

What to do? Forget GROUP BY! Manually assemble the desired query results in a temporary table from simple indexed queries, in this case, two rows per ID:

INSERT INTO earliers 

  SELECT id,entrydate FROM test WHERE id=3 ORDER BY entrydate LIMIT 2;




You need one INSERT statement per grouping value. To print the result, just query the earliers table:

+------+------------+

DROP TEMPORARY TABLE earliers;



Most useful reports run again and again. If that's the case for yours, automate it in a stored procedure: using a cursor and a prepared statement, auto-generate an INSERT statement for every grouping value, and return the result:

  DROP TEMPORARY TABLE earliers;

END;

|

DELIMITER ;

CALL listearliers();




CREATE TABLE games(id INT, teamID INT, score INT);

INSERT INTO games VALUES 

  (1,1,3),(2,1,4),(3,1,5),(4,1,6),(5,2,6),

  (6,2,7),(7,2,8),(8,2,7),(9,2,6),(10,2,7);




How would we write a query that returns the average of the top 50% of scores per team?

The per-team median value is its middle value--lower than the highest 50% and higher than the lowest 50% of values for that team--so a shortcut is to query the team medians, then aggregate on a join that selects per-team scores above the medians.

How to find per-team medians? If a resultset has an odd number of rows, at least one row has the true median score. If it has an even number of rows, the median score is an average of two central values. The following query adapts Joe Celko's formula in "SQL for Smarties" averaging "low" and "high" medians:

+--------+--------+



Now join games to medians accepting only top-half values:

+--------+----------+

DROP TABLE medians;



Yes, all the logic can be moved into one query:

+--------+------+----------------+--------------+




   AND SUM(CASE WHEN l2.hours >= l1.hours THEN 1 ELSE 0 END) >= (COUNT(*)/2) + 1;




An anonymous reader pointed out that this will cost O(N*N), ie it does not scale, so we posted a MySQL implementation of Torben Mogenson's algorithm for calculating the median (http://ndevilla.free.fr/median/median/node20.html), which is said to be amongst the fastest. It also proved too slow. Now Joe Wynne has offered an algorithm which appears to be correct, and which does scale. Here it is as a MySQL stored procedure:



Why don't we make it a function? Because MySQL functions do not (yet?) allow dynamic SQL.

FROM child

GROUP BY pid

ORDER BY frequency DESC

LIMIT 1;




INSERT INTO votes VALUES

  ('Smith',10),('Jones',15),('White',20),('Black',40),('Green',50),('Brown',20);



The query is a two-step:
1. Join the table to itself on the value to be ranked, handling ties
2. Group and order the result of the self-join on rank:

| Jones |    15 |    5 |

| Smith |    10 |    6 |

+-------+-------+------+




Suppose you want to know the vote count for White and the names and tallies for the next highest and next lowest counts:

| Brown |    20 |    3 |            |

+-------+-------+------+------------+




returns 1, as does...

SELECT 'a\\b' RLIKE 'a\\\\b'; 



because in a pair of backslashes, the second is not escaped by the first, so to compare two literals you double each backslash in the RLIKE argument. But if you are querying a table for such a string from the MySQL client, this doubling happens twice--once in the client, and once in the database--so to find a column value matching 'a\\b', you need to write...

SELECT desc FROM xxx WHERE desc RLIKE 'aa\\\\\\\\bb';



That's eight backslashes to match two!

GROUP BY id, col1, col2, col3, ...

HAVING COUNT(*) = 1

ORDER BY ID;




| 70%  | 2008-05-10 |

+------+------------+



We want to retrieve only rows whose `p` values differ from immediately previous values (marked by * above). As with running sums. we get the desired listing by tracking row-to-row value changes with user variables:

| 50%         | 2008-05-08 |

| 70%         | 2008-05-09 |

+-------------+------------+






and here is one for age in years to two decimal places (ignoring day of month):

ROUND((((YEAR(now()) - YEAR(@dob)))*12 + (((MONTH(now()) - MONTH(@dob)))))/12, 2)




This is a variant of the [Not] Exists query pattern. Though we can write it with subqueries, performance will be crisper with a join. But finding data that is not there requires a join to data which is there. So in addition to tables for appointments, doctors and patients, we need a table of all possible appointment datetimes. Here's a schema illustrating the idea ...

  pid INT,                  -- patient id

  d DATE,

  t TIME

);



Now we can apply the [Not] Exists query pattern. To find free appointment datetimes for a given doctor in a given datetime range, we left join possible appointments to existing appointments on date and time and doctor, add Where conditions for desired appointment datetimes, and finally add a Where condition that the appointment slot be null, i.e. free...

ORDER BY d1, d2;



For dates that span different years, week numbers won't work. The answer the number of raw days, minus twice the number of whole weeks (because there are two weekend days/week), minus the number of weekend days in any remainder part-week. This algorithm works when the start and stop dates are themselves business days (but needs refinement to work when passed weekend dates--anybody want to try?):

                          )

                      )

                  );

SELECT FLOOR(@days - @wkndDays) AS BizDays;



The algorithm is easily encapsulated in a function:

                           )

                       )

                   );

  RETURN FLOOR(days - wkndDays);

END;

|

DELIMITER ;



To include time in the difference, you would probably adopt the convention of returning a string like N days hh:mm:ss where N is the date difference calculated above, minus one if the time portion of d1 is later than that of d2:

+------------------+

| dtdiff           |

+------------------+

| 64 days 11:00:00 |

+------------------+



To factor in national and religious holidays, you need a holidays table and a stored procedure that adds in the number of holidays between d1 and d2.

An earlier formula we had for this problem sometimes gave incorrect results. As a debugging aid, we wrote a brute force calculator for the problem:



You would not want to use that function on long date spans in a big table, but it will do for testing.

Now, how to count the number of Tuesdays, say, between two dates? The basic logic is:

1. Count weeks between the two dates.

2. If beginning and ending weekdays are the same, then if they're Tuesday, the answer is weeks+1, otherwise it's just weeks.

3. Otherwise, if the beginning weekday <= the ending weekday, then if Tuesday is between them, the answer is weeks+1, otherwise it's just weeks.

4. Otherwise the ending weekday is less than the starting weekday; if Tuesday >= the starting weekday or <= the ending weekdayy, the answer is weeks+1, otherwise it's just weeks.

For a convenient datasource, we'll use the two date columns orderdate and shippeddate in the orders table of the NorthWind database, and we'll use our brute force function DayCount() to check results:

  DayCount(DATE(orderdate),DATE(shippeddate),@day) AS Chk

FROM orders

HAVING !ISNULL(res-chk) AND res-chk <> 0;

Empty set (0.00 sec)



No errors. We get the same result for @day = 1, 2, 4, 5, 6 and 7.

But the formula is buried in the specifics of one table, so abstract it to a reusable function:

END;

|

DELIMITER ;



Again check it against lots of date value pairs:

FROM orders

HAVING !ISNULL(diff) AND diff <> 0;

Empty set (0.00 sec)




  AND MONTH(date) = IF( MONTH(@d) = 12, 1, MONTH(@d) + 1 )

  AND WEEKDAY(date)=4;




+--------------+

| 2008-07-14   |

+--------------+




UNIX_TIMESTAMP( dt2 ) - UNIX_TIMESTAMP( dt1 )



To get the number of minutes divide by 60, for the number of hours divide by 3600, and for the number of days, divide by 3600 * 24.

|    3 |          1 | 2007-02-10 | 2007-02-17 |

+------+------------+------------+------------+



Reservation systems usually adopt the closed-open convention of representing when reservations begin and end. For example, if you book a hotel room for 22 May through 24 May, the hotel expects you to stay overnight on 22 May and 23 May, but not on 24 May. Apart from that difference, this is the same pattern as Finding missing numbers in a sequence.

| 2007-01-31     | 2007-02-10 |

+----------------+------------+



This query cannot see reservation dates earlier than the first existing reservation date, or later than the last. Usually, you would want a calendar table to provide those limits, but you can fake them with a union. If the allowable reservation period is 1 Dec 2006 through 1 Jul 2007, union the left side of the join with a made-up row for 1 Dec 2006, and union the right side of the join with a made-up row for 1 Jul 2007:

(5, '2008-09-01 16:19', '2008-09-01 16:25'),

(6, '2008-09-01 17:52', '2008-09-01 17:59'),

(7, '2008-09-01 18:18', '2008-09-01 18:22');




There are two overlap periods in this data:



One solution is to use a View to identify starting and stopping events, then define an Overlaps View:

+---------------------+------------+

| 2008-09-01 15:02:00 |          2 |

| 2008-09-01 16:19:00 |          2 |

+---------------------+------------+



But that doesn't show us when overlap periods end. There is a fuller and more straightforward solution: join visits to itself on the criteria that

(i) the first of each joined pair of visits started no earlier than the second,
(ii) the first visit started before the second ended, and
(iii) the second visit started before the first ended:

| 5    | 2008-09-01 16:19:00 | 4    | 2008-09-01 16:23:00 |

+------+---------------------+------+---------------------+




If a table has columns processID, start_date and end_date, those three columns are period unique if there exists no pair of rows with the same processID and overlapping start_date and end_date values. If there is such a pair of rows, the table exhibits sequenced duplication.

Another way of saying it: if an instant is the smallest datetime unit of start_date and end_date columns, then if there are no sequenced duplicates, there is exactly one processID value at any instant.

Here is a query to find sequenced duplicates for those columns:

|    2 |          1 | 2007-01-20 | 2007-01-31 |

+------+------------+------------+------------+



You need a query indicating whether a given property is available for a given period of time.

Hotels & property renters usually adopt what is called the 'closed-open' convention for bookings, eg a booking from 22 May through 24 May means you sleep there the nights of 22 and 23 May. To show that property P is available for the desired closed-open period dStart to dEnd, you need to prove there is no booked period for P that overlaps dStart through dEnd. Until you're used to thinking about periods, it's easier to analyse graphically. There are four ways a booked reservation can overlap the desired date range ...

             dStart        dEnd

             |----------------|

        startDate            endDate

        |--------------------------|       

        |------| 

                   |----| 

                            |------|



but there are just two ways a booked reservation can not overlap:

             dStart        dEnd

             |----------------|

       |-----|                |-----|

     |-----|                    |-----|



So the period dStart through dEnd is available if there is no row where ...



or equivalently ...

endDate > dStart AND startDate < dEnd



Here is a simple stored procedure for testing the query:

+-----------+

| Available |

+-----------+

| No        |

+-----------+

1 row in set (0.00 sec)




  @refday - INTERVAL @subtract DAY AS LastBizDay

FROM ... etc




);



An elegant method of generating any desired number of sequential values, posted by Giuseppe Maxia on his blog, is ...

• Create three dummy rows in a View.
• Cross join them to make 10 dummy rows.
• Cross join those to make 100, 1,000 or however many you need.

So to give the calendar table a million rows at one-hour intervals starting on 1 Jan 1970:



If your MySQL version does not support Views, or if you prefer to avoid writing the View joins, make a table of integers 1 through 10...

create table ints(i int);

insert into ints values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);



and join them as we did with the Views above.

Here is a variation on Giuseppe's method (written about also by Baron Schwartz and Rob Wultsch) to make a table of a thousand dates starting today:

JOIN digits AS v

WHERE ( t.i*100 + u.i*10 + v.i ) < 1000;



You can use such a SELECT as a View, as an inline derived table, or as input to a CREATE TABLE statement.

A slightly more elaborate method, giving the calendar table an auto-increment key that can also be used as a surrogate for datetime interval calculations:

  );



Calculate the number of days needed in the calendar, eg

  SELECT DATEDIFF('2010-12-31','1989-12-31');   # 7670, or 21*365 plus 5




Find a table with that many rows, 7670 in this case. Add a row to the calendar table for every day in the range:



Populate the date column by incrementing the starting date:

  UPDATE calendar SET date = ADDDATE('1989-12-31',id);



The calendar table now has one row for each day from 1990-01-01 through 2010-12-31. Keep the auto_increment ID column for quick day counts in the range, or drop the column if you don't need that.

To make the calendar table a diary, make the period one leap year, add month, day and text columns, update month and day values with MONTH(date) and DAYOFMONTH(date) respectively, and if the diary is to be used from year to year, drop the date field.
[Based on a builder.com SQL Tip by Arthur Fuller and a MySQL list tip by Michael Stassen]

To automate all this, write a stored procedure, for example:



Or, you can have the sproc do your counting:

CALL calendar('2007-1-1 00:00:00', '2007-2-1 00:00:00', 20);




ORDER BY OverlapCount DESC;




|           4 | 2007-01-01 14:00:00 | 2007-03-07 19:00:00 |   20 |

+-------------+---------------------+---------------------+------+



In this table, att is a delta: it tracks entrances. Actual attendance is SUM(att) at any given moment. For example, if the attendance sum is x at a given moment, then after we add a row with att=y and start/end datetimes embracing that moment, attendance will be x+y. So to retrieve total attendance at 01330h on 1 Jan 2007, we write:

+----------+

| SUM(att) |

+----------+

|       25 |

+----------+



Then how would we extract maximum attendance during a given period, for example, maximum attendance between 1300h and 1700h?

SQL does not deal efficiently with time. Some SQL dialects offer time series enhancements to the language; MySQL does not.

And, querying time series data for aggregate statistics gets complicated very quickly.

It gets a bit simpler with a calendar table that has a row for every possible datetime value. For our example, assume a granularity of one hour and a query period of one day. Naturally a real system would require a range of dates and perhaps a finer time granularity:

insert into cal values(22,'2007-1-1 22:00:00');

insert into cal values(23,'2007-1-1 23:00:00');

insert into cal values(24,'2007-1-1 24:00:00');




To accumulate the maximum attendance sum, collect target values for defined periods in an inner query, and sum them from the outer query:

+------------+

|         45 |

+------------+



If the data is more complicated, eg if we also need to track exits, the period logic needs refinement but the principle remains the same.



Summing values like '12:65:23' produces meaningless results.

+------------+---------+------------+

| 2008-03-10 |       2 | 2008-03-13 |

+------------+---------+------------+




FROM teams AS t1

STRAIGHT_JOIN teams AS t2

WHERE t1.ID <> t2.ID;






MAX() chooses existing over blank entries, and GROUP BY lines everything up on the same row.

[CONSTRAINT [constraint_name]] 

FOREIGN KEY [key_name] (keycol_name,...) reference_definition



and the syntax for dropping one is ...



Notice that you can omit the CONSTRAINT when you declare a foreign key, but the only way to DROP a foreign key is to reference it by the constraint_name which you probably never specified!

There should be a circle of hell reserved for designers who build inconsistencies like this into their tools. The only way round this one is to run SHOW CREATE TABLE to find out what the foreign key's constraint_name is, so you can write the DROP statement. Here is a wee test case:

drop table if exists a,b;
create table a(i int primary key)engine=innodb;
create table b(i int,foreign key(i) references a(i)) engine=innodb;
show create table\G

CREATE TABLE `b` (
  `i` int(11) DEFAULT NULL,
  KEY `i` (`i`),
  CONSTRAINT `b_ibfk_1` FOREIGN KEY (`i`) REFERENCES `a` (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

-- drop and recreate the FK:
alter table b drop foreign key b_ibfk_1;
alter table b add foreign key(i) references a(i) on update cascade;
show create table b\G

Create Table: CREATE TABLE `b` (
  `i` int(11) DEFAULT NULL,
  KEY `i` (`i`),
  CONSTRAINT `b_ibfk_1` FOREIGN KEY (`i`) REFERENCES `a` (`i`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

drop table a,b;

• write temp tables collecting desired I_S metadata on each database
• map the compare-data query template to those two metadata tables

• Before calling the sproc, the new database must have been created.
• The procedure refuses to rename the mysql database.
• The old database is left behind, minus what was moved.

• null out path columns in the nodes table
• find the nodeIDs referenced by input params
• loop through all uncalculated one-step paths, calculating costs in each
• if a node remains uncalculated, the graph is invalid, so quit
• write the path sequence to a temporary table
• query the temp table to show the result

DROP PROCEDURE IF EXISTS dijResolve;
DELIMITER |
CREATE PROCEDURE dijResolve( pFromNodeName VARCHAR(20), pToNodeName VARCHAR(20) )
BEGIN
  DECLARE vFromNodeID, vToNodeID, vNodeID, vCost, vPathID INT;
  DECLARE vFromNodeName, vToNodeName VARCHAR(20);
  -- null out path info in the nodes table
  UPDATE dijnodes SET PathID = NULL,Cost = NULL,Calculated = 0;
  -- find nodeIDs referenced by input params
  SET vFromNodeID = ( SELECT NodeID FROM dijnodes WHERE NodeName = pFromNodeName );
  IF vFromNodeID IS NULL THEN
    SELECT CONCAT('From node name ', pFromNodeName, ' not found.' ); 
  ELSE
    BEGIN
      -- start at src node
      SET vNodeID = vFromNodeID;
      SET vToNodeID = ( SELECT NodeID FROM dijnodes WHERE NodeName = pToNodeName );
      IF vToNodeID IS NULL THEN
        SELECT CONCAT('From node name ', pToNodeName, ' not found.' );
      ELSE
        BEGIN
          -- calculate path costs till all are done
          UPDATE dijnodes SET Cost=0 WHERE NodeID = vFromNodeID;
          WHILE vNodeID IS NOT NULL DO
            BEGIN
              UPDATE 
                dijnodes AS src
                JOIN dijpaths AS paths ON paths.FromNodeID = src.NodeID
                JOIN dijnodes AS dest ON dest.NodeID = Paths.ToNodeID
              SET dest.Cost = CASE
                                WHEN dest.Cost IS NULL THEN src.Cost + Paths.Cost
                                WHEN src.Cost + Paths.Cost < dest.Cost THEN src.Cost + Paths.Cost
                                ELSE dest.Cost
                              END,
                  dest.PathID = Paths.PathID
              WHERE 
                src.NodeID = vNodeID
                AND (dest.Cost IS NULL OR src.Cost + Paths.Cost < dest.Cost)
                AND dest.Calculated = 0;
       
              UPDATE dijnodes SET Calculated = 1 WHERE NodeID = vNodeID;

              SET vNodeID = ( SELECT nodeID FROM dijnodes
                              WHERE Calculated = 0 AND Cost IS NOT NULL
                              ORDER BY Cost LIMIT 1
                            );
            END;
          END WHILE;
        END;
      END IF;
    END;
  END IF;
  IF EXISTS( SELECT 1 FROM dijnodes WHERE NodeID = vToNodeID AND Cost IS NULL ) THEN
    -- problem,  cannot proceed
    SELECT CONCAT( 'Node ',vNodeID, ' missed.' );
  ELSE
    BEGIN
      -- write itinerary to map table
      DROP TEMPORARY TABLE IF EXISTS map;
      CREATE TEMPORARY TABLE map (
        RowID INT PRIMARY KEY AUTO_INCREMENT,
        FromNodeName VARCHAR(20),
        ToNodeName VARCHAR(20),
        Cost INT
      ) ENGINE=MEMORY;
      WHILE vFromNodeID <> vToNodeID DO
        BEGIN
          SELECT 
            src.NodeName,dest.NodeName,dest.Cost,dest.PathID
            INTO vFromNodeName, vToNodeName, vCost, vPathID
          FROM 
            dijnodes AS dest
            JOIN dijpaths AS Paths ON Paths.PathID = dest.PathID
            JOIN dijnodes AS src ON src.NodeID = Paths.FromNodeID
          WHERE dest.NodeID = vToNodeID;
          
          INSERT INTO Map(FromNodeName,ToNodeName,Cost) VALUES(vFromNodeName,vToNodeName,vCost);
          
          SET vToNodeID = (SELECT FromNodeID FROM dijPaths WHERE PathID = vPathID);
        END;
      END WHILE;
      SELECT FromNodeName,ToNodeName,Cost FROM Map ORDER BY RowID DESC;
      DROP TEMPORARY TABLE Map;
    END;
  END IF;
END;
|
DELIMITER ;
CALL dijResolve( 'a','i');
+--------------+------------+------+
| FromNodeName | ToNodeName | Cost |
+--------------+------------+------+
| a            | b          |    4 |
| b            | c          |    6 |
| c            | j          |   18 |
| j            | f          |   26 |
| f            | i          |   37 |
+--------------+------------+------+

• Open Item: match payments against individual charges, typically by carrying the charge number in the payments table
,
• Statement: list and sum all charges and all payments, and show the difference as the outstanding balance.

• all rows from a, with matching rows or nulls from b, and
• all rows from b, with matching rows or nulls from a

• an INNER JOIN between a and b to catch row matches between a and b,
• a LEFT EXCLUSION JOIN from a to b to catch rows that are in a and not in b,
• a RIGHT EXCLUSION JOIN from b to a to catch rows in b that are not in a.