Left join ?

[Cedric Donckels]

Hi everybody

I have two tables
the first one represent all messages sent on a network with attribute the time where the message was created (TimeCreated) and a type code (Shortnumber).

So, when i ask the db for the messages sent between to date; it returns me only rows for the days where messages where sent.
THe problem is that i wanted to see too the days with no messages .
My idea was to created another talbe (dates) with all dates between 2000-01-01 and 2030-12-31 (attribute timestamp) and make a left join with these to tables.

"SELECT DATE_FORMAT(timestamp,'%d/%m') Jour, COUNT(MsgId) 'Nb messages' FROM dates LEFT JOIN queues ON dayofyear(timestamp)=dayofyear(timecreated) WHERE timestamp>='2005-03-01 ' AND timestamp<'2005-07-14 'AND shortnumber='3099' GROUP BY MONTH(timestamp),DAYOFMONTH(timestamp); "

but, the result is the same as before, i've got entry for march 7 and 8 and so i see only the two rows for these two days on not rows for march 9 - 14 with 'nb message' =null.

Can somebody tells me what I forget

Thanks