Porl123 Posted June 29, 2010 Share Posted June 29, 2010 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! Link to comment https://forums.phpfreaks.com/topic/206131-left-join-with-where-clause/ Share on other sites More sharing options...
kickstart Posted June 29, 2010 Share Posted June 29, 2010 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 Link to comment https://forums.phpfreaks.com/topic/206131-left-join-with-where-clause/#findComment-1078631 Share on other sites More sharing options...
luca200 Posted June 29, 2010 Share Posted June 29, 2010 Replacing WHERE with HAVING should be enough Link to comment https://forums.phpfreaks.com/topic/206131-left-join-with-where-clause/#findComment-1078668 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.