How to perform a left join of list of dates with an existing table of records smartly

Question

I am using the following query to get a list of dates between two given dates:

SELECT selected_date FROM 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
WHERE selected_date BETWEEN '2016-03-08' AND '2016-03-11';

This gives me the following output:

+---------------+
| selected_date |
+---------------+
| 2016-03-08    |
| 2016-03-09    |
| 2016-03-10    |
| 2016-03-11    |
+---------------+

The following query gives me a list of all employees required to work for a particular date:

SELECT employee.id, employee.firstname
FROM Users employee
LEFT JOIN Table1 table1 ON table1.fId = employee.tId
WHERE table1.day = dayofweek('2016-03-10')

This yields:

+----+-----------+
| id | firstname |
+----+-----------+
|  1 | Mat       |
|  2 | Tom       |
+----+-----------+

The results are empty if no employee is required to work on the given date.

I have a thrid table which lists the performance of each employee for a particular day:

SELECT * FROM performance;

+----+------------+------------+------+
| id | employeeId |    date    | val  |
+----+------------+------------+------+
|  1 |          1 | 2016-03-08 | 100  |
|  2 |          2 | 2016-03-08 | 90   |
|  3 |          1 | 2016-03-09 | 80   |
|  4 |          2 | 2016-03-09 | 100  |
+----+------------+------------+------+

I want to join the above three results to yield:

+----+------------+-----------+------------+------+
| id | employeeId | firstname |    date    | val  |
+----+------------+-----------+------------+------+
|  1 |          1 | Mat       | 2016-03-08 | 100  |
|  2 |          2 | Tom       | 2016-03-08 | 90   |
|  3 |          1 | Mat       | 2016-03-09 | 80   |
|  4 |          2 | Tom       | 2016-03-09 | 100  |
|  5 |          1 | Mat       | 2016-03-10 | null |
|  6 |          2 | Tom       | 2016-03-10 | null |
+----+------------+-----------+------------+------+

Note : On 2016-03-10 Mat and Tom aren't required to work. Hence, their values should be null in the final result. The performance table will not have entries for Mat and Tom for 2016-03-10 in this case.

I am kind of lost on how to achieve this.

Answer 1

You must CROSS JOIN the Users table with your Calendar query.

It will give you this result with one of each date for each user:

selected_date | firstname
2016-03-08    | Mat
2016-03-09    | Mat
2016-03-10    | Mat
2016-03-11    | Mat
2016-03-08    | Tom
2016-03-09    | Tom
2016-03-10    | Tom
2016-03-11    | Tom

This can then be LEFT JOIN with the Performance table. (SQL Fiddle)

Query:

SELECT u.id, u.firstname, cal.selected_date, p.val
FROM Users u
CROSS JOIN (
    SELECT selected_date FROM (
        SELECT adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date 
        from            
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
            (SELECT 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
    ) v
    WHERE selected_date BETWEEN '2016-03-08' AND '2016-03-11'
) cal
LEFT JOIN performance p
    ON u.id = p.employeeId AND cal.selected_date = p.date;

Output:

 id | firstname | selected_date |    val 
  1 |       Mat |    2016-03-08 |    100 
  2 |       Tom |    2016-03-08 |     90 
  1 |       Mat |    2016-03-09 |     80 
  2 |       Tom |    2016-03-09 |    100 
  1 |       Mat |    2016-03-10 | (null) 
  2 |       Tom |    2016-03-10 | (null) 
  1 |       Mat |    2016-03-11 | (null) 
  2 |       Tom |    2016-03-11 | (null) 

Answer 2

Build a table with a million integers. Then JOIN to that table for things like this.

In MariaDB (10.0.3 or later), it is much simpler: JOIN seq_1_to_1234 -- a magical table that has all the ints from 1 to 1234, or whatever range you like. Ref page and Ref.