Account for LEFT ENTRY AND WHERE

Question

I have a little problem with the left join, where I need a list of projects, and for each design I want to display how many comments each construct has.

I use 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 this does not work, since it displays only one project that has 1 comment, but I have two projects in the table where the second design has no comment.

If I changed 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 

This is the removal of the WHERE clause. But this is bad, because he will choose both projects and comments that have not been approved.

What will I miss / do wrong?

Answer 1

Move all filters in comments to 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

When you use the WHERE , these restrictions apply to strings provided by left join s. Thus, some lines are deleted if they are not consistent with the restrictions.

What you need to do is put these restrictions in the ON clause, and it should work so if it doesn't work, it shows NULL instead of deleting the line.

Something like that:

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

---

Another problem is using ds.* In SELECT when you have ds.id on group BY .

Answer 3

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

As suggested by others, you can fix this by moving this condition to the ON clause, but I don’t really like it because it is not a join condition.

I would move the filters in the external table to the 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 

Answer 4

I think this post will really help: https://lasserafn.com/faster-mysql-counting-on-joins/