MySQL select date, join statistic data - how?

Question

I have some statistics I need to report on a monthly basis. I need my query to return 0's for statistics which aren't there. The only way I know to do this is to use SQL to generate a list of days within the specified time period and LEFT JOIN the statistic data that I need to report on.

Problem is.... I've never not selected from a table like that... how do I do that?

Answer 1

Compare your returned values, for statistic that never existed your returned value probably will be null, all you need to compare returned value with null and if null return 0 otherwise return real result

select s.Name , IFNULL(d.Value, 0) as Value
 from Statustic s
LEFT JOIN t_Data d on (d.Stat_ID=s.ID)

and you can use this link to read more at http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_isnull

Answer 2

If the values are NULL use

SELECT IFNULL( statistic, 0 ) AS statistic FROM myTable;

Answer 3

I ended up just using SQL to pull the data I have for the given period and handling everything else on the PHP side. Not exactly what I wanted, but it works.