How to get MYSQL to show Zero results when left joining to self? (eg: Monthly Stats)

Question

I have a table with typical statistics on page view and emails. An email creates a row and page view creates a row...

I want to chart the page views and emails on a daily basis for the past 30 days (for example) so I need to show the day and the value... even if it ZERO. I cannot have missing days from my results or the chart breaks.

PageViews Table:
UniqueID | PageID | DateTime

Emails Table:
UniqueID | SenderEmail | RecipientID | RecipientEmail | DateTime

I don't need this to be one query but the problem remains the same for either query... If there are days (or hours, or months.. depending on the group by range) where a specific page was not viewed it is simply omitted from the results array.

Similarly, if there there are days where there were page views but now emails, then the email count query has holes in it...

Essentially, I am trying to create a Google Analytics type chart and need the lines to show ZERO for the days where there are no data in my stats tables...

I think some kind of left self join might work but cannot seem to make it work. I even messed around with an integer table with only one column (values) and rows for 1-31

Can any of you MYSQL gurus help?

Answer 1

The standard solution for this problem is to create a helper table and populate it with all of the dates relevant to your anticipated queries.

Here's an applicable SO Q&A:

http://stackoverflow.com/questions/1877874/how-to-fill-date-gaps-in-mysql