select - Selecting Non-existent Data With MySQL

Question

I'm trying to select data between two date range. However not all data are being inserted daily. Below is sample of the table:

mysql> SELECT * FROM attendance;
+------------+-------+
| date       | total |
+------------+-------+
| 2012-07-02 |   100 |
| 2012-07-04 |    70 |
| 2012-07-05 |    78 |
+------------+-------+
3 rows in set (0.00 sec)

The scenario is I want to get total of attendance from 2012-07-02 till 2012-07-04. Based on the data above I will get

mysql> SELECT * FROM attendance WHERE date BETWEEN '2012-07-02' AND '2012-07-04';
+------------+-------+
| date       | total |
+------------+-------+
| 2012-07-02 |   100 |
| 2012-07-04 |    70 |
+------------+-------+
2 rows in set (0.00 sec)

However my objective is to have 2012-07-03 included in the result.

+------------+-------+
| date       | total |
+------------+-------+
| 2012-07-02 |   100 |
| 2012-07-03 |     0 |
| 2012-07-04 |    70 |
+------------+-------+

Is this possible to be done through MySQL? I did look into temporary table. But still unable to achieve the objective.

Answer 1

You can enumerate dates as derived pseudo-table (with UNION) and then join it with your data

SELECT dates.date, COALESCE(attendance.total,0) AS total FROM (
SELECT '2012-07-02' AS date
UNION ALL SELECT '2012-07-03'
UNION ALL SELECT '2012-07-04'
) AS dates
LEFT JOIN attendance USING(date)

Edit: added COALESCE to return 0 instead of NULL on missing records.