Using Joins and Subqueries - MySQL - page 506

The example just shown uses the reference table with a LEFT JOIN to fill holes in the
summary. It's also possible to use the reference table to detect holes in the dataset—that
is, to determine which categories are not present in the data to be summarized. The
following statement shows those dates on which no driver was active by looking for
reference rows for which no driver_log table rows have a matching category value:
mysql> SELECT dates.d
-> FROM dates LEFT JOIN driver_log ON dates.d = driver_log.trav_date
-> WHERE driver_log.trav_date IS NULL;
+------------+
| d |
+------------+
| 2014-07-28 |
| 2014-07-31 |
+------------+
Reference tables that contain a list of categories are quite useful in summary context, as
just shown. But creating such tables manually is mind-numbing and error-prone. A
stored procedure that uses the endpoints of the range of category values to generate the
reference table for you helps automate the process. In essence, this type of procedure
acts as an iterator that generates a row for each value in the range. The following pro‐
cedure, make_date_list() , shows an example of this approach. It creates a reference
table containing a row for every date in a particular date range. It also indexes the table
so that it will be fast in large joins:
CREATE PROCEDURE make_date_list ( db_name TEXT , tbl_name TEXT , col_name TEXT ,
min_date DATE , max_date DATE )
BEGIN
DECLARE i , days INT ;
SET i = 0 , days = DATEDIFF ( max_date , min_date ) + 1 ;
# Make identifiers safe for insertion into SQL statements . Use db_name
# and tbl_name to create qualified table name .
SET tbl_name = CONCAT ( quote_identifier ( db_name ), '.' ,
quote_identifier ( tbl_name ));
SET col_name = quote_identifier ( col_name );
CALL exec_stmt ( CONCAT ( 'DROP TABLE IF EXISTS ' , tbl_name ));
CALL exec_stmt ( CONCAT ( 'CREATE TABLE ' , tbl_name , '(' ,
col_name , ' DATE NOT NULL, PRIMARY KEY(' ,
col_name , '))' ));
WHILE i < days DO
CALL exec_stmt ( CONCAT ( 'INSERT INTO ' , tbl_name , '(' , col_name , ') VALUES(' ,
QUOTE ( min_date ), ' + INTERVAL ' , i , ' DAY)' ));
SET i = i + 1 ;
END WHILE ;
END ;
Use make_date_list() to generate the reference table, dates , like this:
CALL make_date_list ( 'cookbook' , 'dates' , 'd' , '2014-07-26' , '2014-08-02' );