CODEWITHSUNDEEP
mysql
Jason
Jason

Reputation: 363

mysql display each day in a month

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

Upvotes: 1

Views: 427

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166396

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.

Upvotes: 1

Related Questions