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

MySQL

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.

Best Answer

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)
Related Question