count in LEFT JOIN and WHERE

Question

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?

Answer 1

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

Answer 2

The problem is that when you include com.approved = 1 in the WHERE clause, it filters out any rows where com.approved is NULL -- meaning all the rows where no match was found in the outer table.

As suggested by others, you can fix this by moving that condition to the ON clause, but I don't really like that since it's not a join condition.

I would move the filters on the outer table into an inline view

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