LEFT JOIN with WHERE clause

[Porl123]

Hi my MySQL version is 5.0.67 and I have a problem when I join my forum topics table with my forum replies table. The statement I'm having the problem with is on the forum topics page. What I want it to do is to go through the topics table and echo out the subjects (titles), author name and reply count, so I used LEFT JOIN so I wouldn't need a separate query per topic to check the post count. The problem is that it seems to disregard the WHERE clause where I only want it to show topics where del = 0 (not deleted) however it still posts the row anyway, just with blank fields, then 0 where the reply count should be. Does anyone know what I'm doing here to make it post the row?

 

SELECT `forumTopics`.`id`, `forumTopics`.`subject`, `forumTopics`.`author`, COUNT(`forumReplies`.`id`) FROM `forumTopics` LEFT JOIN `forumReplies` ON `forumTopics`.`id` = `forumReplies`.`t_id` WHERE `forumTopics`.`del` = 0

 

I'm fairly new to MySQL so I'm sorry if I'm explaining this all wrong. :c thanks!

[kickstart]

Hi

 

Not quite sure I understand what the problem is, but suspect it might be down to grouping. Think you need a GROUP BY clause.

 

All the best

 

Keith

[luca200]

Replacing WHERE with HAVING should be enough