mysql> SELECT d.day AS day FROM dateref d WHERE day BETWEEN '2007-03-15' AND '2007-03-20'; +------------+
| day |
+------------+
| 2007-03-15 |
| 2007-03-16 |
| 2007-03-17 |
| 2007-03-18 |
| 2007-03-19 |
| 2007-03-20 |
+------------+
6 rows in set (0.01 sec)
mysql> SELECT DISTINCT DATE(logdate) FROM logtable WHERE src='foo' AND DATE(logdate) BETWEEN '2007-03-15' AND '2007-03-20';
+-------------+
| DATE(added) |
+-------------+
| 2007-03-15 |
| 2007-03-16 |
| 2007-03-18 |
| 2007-03-19 |
| 2007-03-20 |
+-------------+
5 rows in set (0.10 sec)
mysql> SELECT d.day, COUNT(l.id) as hits FROM dateref d LEFT JOIN logtable l ON d.day = date(l.logdate) WHERE src = 'foo' AND d.day BETWEEN '2007-03-15' AND '2007-03-20' GROUP BY d.day;
+------------+------+
| day | hits |
+------------+------+
| 2007-03-15 | 3 |
| 2007-03-16 | 3 |
| 2007-03-18 | 1 |
| 2007-03-19 | 7 |
| 2007-03-20 | 3 |
+------------+------+
5 rows in set (0.02 sec)
mysql>