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! Quote 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 Quote 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 Quote 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
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.