title: count in LEFT JOIN and WHERE
body: <p>I have a little problem with a left join where I want a list of designs and on each design I want to display how many comments each design have.</p>
  
  <p>I am using a LEFT JOIN</p>
  
  <pre><code>SELECT ds.*, count(com.comment) AS countcom FROM tdic_designs ds 
  LEFT JOIN tdic_comments com ON (com.design_id = ds.id) 
  WHERE ds.approved = 1 AND ds.hidden = 0 AND com.approved = 1 
  GROUP BY ds.id 
  ORDER BY ds.date_added ASC
  </code></pre>
  
  <p>But that doesn't work as it only displays one design which have 1 comment, but I have two designs in the table, where the second design doesn't have a comment.</p>
  
  <p>If I change the SQL to</p>
  
  <pre><code>SELECT ds.*, count(com.comment) AS countcom FROM tdic_designs ds 
  LEFT JOIN tdic_comments com ON (com.design_id = ds.id) 
  GROUP BY ds.id, com.approved, ds.approved 
  ORDER BY ds.date_added ASC
  </code></pre>
  
  <p>That is removing the WHERE clause. But that is bad as it will select both designs and comments that haven't been approved.</p>
  
  <p>What do I miss / do wrong?</p>