count in LEFT JOIN and WHERE

group-by, left-join, select, sql

Solution

Move all filters on comments to the `ON` clause:

SELECT  ds.*, COUNT(com.design_id) AS countcom
FROM    tdic_designs ds
LEFT JOIN
        tdic_comments com
ON      com.design_id = ds.id
        AND com.approved = 1 
WHERE   ds.approved = 1
        AND ds.hidden = 0
GROUP BY
        ds.id
ORDER BY
        ds.date_added ASC

Problem

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. I am using a LEFT JOIN ``` 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 ``` 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. If I change the SQL to ``` 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 ``` That is removing the WHERE clause. But that is bad as it will select both designs and comments that haven't been approved. What do I miss / do wrong?

Original source