mysql display each day in a month

Question

during a month, display the infor each date, order by date, but this infor is empty in some day. how can i still display each day as a row?

Product  date
-----------------
20     2008-01-01
10     2008-01-02
20     2008-01-03
10     2008-01-05
09     2008-01-08
30     2008-01-09

result:

Product  date
-----------------
20     2008-01-01
10     2008-01-02
20     2008-01-03
0      2008-01-04
10     2008-01-05
0      2008-01-06
0      2008-01-07
09     2008-01-08
30     2008-01-09

Answer 1

In MySQL, unfortunately, you might have to have a table that contains the dates pre populated to achieve this.

You will have to then left join to this table to retrieve all the appropriate dates.

In SQL Server 2005+ you could have achieved this using a CTE statement, to recursively generate the dates required.