MySQL GROUP BY with three tables
- by Psaniko
I have the following tables:
posts (post_id, content, etc)
comments (comment_id, post_id, content, etc)
posts_categories (post_category_id, post_id, category_id)
and this query:
SELECT `p`.*, COUNT(comments.comment_id) AS cmts, posts_categories.*,comments.*
FROM `posts` AS `p`
LEFT JOIN `posts_categories`
ON `p`.post_id = `posts_categories`.post_id
LEFT JOIN `comments`
ON `p`.post_id = `comments`.post_id
GROUP BY `p`.`post_id`
There are three comments on post_id=1 and four in total. In posts_categories there are two rows, both assigned to post_id=1. I have four rows in posts.
But if I query the statement above I get a result of 6 for COUNT(comments.comment_id) at post_id=1. How is this possible? I guess the mistake is somewhere in the GROUP BY clause but I can't figure out where.
Any suggestions?
