title: mysql reporting daily enquiry filtering by type, including missing grouping rows
body: <p>I am a relative newbie and i have been battling with this for too long now - so please can anyone help!</p>
  
  <p>I have a table which receives enquiries(dev_enquiry) for different types of developments and I am creating a dynamic chart to show how many enquiries per day per development. I joined with calendar table which holds a column of dates so that I can get 0 values when there have been no enquiries.</p>
  
  <p>This SQL gives me exactly what I want but its for all the developments </p>
  
  <pre><code>SELECT    calendar.datefield AS DATE, COUNT(de_id) AS enquiries
  FROM      `calendar`  
  LEFT JOIN dev_enquiry ON DATE(dev_enquiry.de_date_created) = calendar.datefield)
  WHERE     calendar.datefield BETWEEN (
                                         SELECT MIN(DATE(de_date_created)) 
                                         FROM   dev_enquiry
                                        ) 
        AND (SELECT MAX(DATE(de_date_created)) FROM dev_enquiry)
  GROUP BY  DATE 
  ORDER BY  DATE DESC
  
  2012-11-08      3
  2012-11-07      0
  2012-11-06      2
  2012-11-05      1
  2012-11-04      3
  </code></pre>
  
  <p><strong>I want to filter out the result by type of development but still  retain the null values when there are no enquiries on that day for that particular development.</strong></p>
  
  <p>The following filters the result but doesn't give me the null values:</p>
  
  <pre><code>SELECT  cal.datefield AS DATE, COUNT(t.de_id)  AS  enquiries
    FROM (
  SELECT * FROM dev_enquiry as d LEFT JOIN calendar as c ON (DATE(d.de_date_created) = c.datefield) 
  WHERE     d.de_dev_name = 'Green') as t
  LEFT JOIN calendar as cal ON (cal.datefield = DATE(t.de_date_created)) 
   WHERE (cal.datefield BETWEEN (SELECT   MIN(DATE(t.de_date_created)) 
  FROM dev_enquiry) AND (SELECT MAX(DATE(de_date_created)) FROM dev_enquiry)) 
  GROUP BY DATE ORDER BY  DATE  DESC
  
  
  2012-11-08      1
  2012-11-06      2
  2012-11-05      1
  2012-11-04      1
  </code></pre>
  
  <p>Results for 2012-11-07 are missing</p>