我有一个左连接的问题,我想要一个设计列表,在每个设计上我想显示每个设计有多少注释.
我正在使用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
但这不起作用,因为它只显示一个有1条评论的设计,但我在表中有两个设计,其中第二个设计没有评论.
如果我将sql更改为
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
那就是删除WHERE子句.但这很糟糕,因为它会选择尚未批准的设计和评论.
我错过了什么/做错了什么？
解决方法
将注释上的所有过滤器移动到ON子句：
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
