MySQL GROUP BY with three tables

Question

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?

Answer 1

My guess is you have more than one category involved!

Does

SELECT P.post_id
     , PC.post_category_id
     , COUNT(C.comment_id) AS cmts
  FROM `posts` AS P 
  LEFT JOIN `posts_categories` as PC
    ON `p`.post_id = `posts_categories`.post_id 
  LEFT JOIN `comments` as C
    ON P.post_id = C.post_id 
GROUP BY P.post_id, PC.post_category_id

give you an 'understandable' result?

BTW You should have all fields in the GROUP BY clause that are not aggregated...

Answer 2

You get six (6) comments because your resultset looks something like this:
(note the two groups of three rows each; one for each post category—abbreviated as pcat*)

post_id  cmts  post_cat  comments
1        6     pcat1     comment text...
1        6     pcat1     comment text...
1        6     pcat1     comment text...
1        6     pcat2     comment text...
1        6     pcat2     comment text...
1        6     pcat2     comment text...

You can do something like this instead:

  SELECT `p`.post_id, 
         COUNT(comments.comment_id) AS cmts, 
         COUNT(posts_categories.) AS categories
    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`

Answer 3

As a first approximation try

SELECT `p`.*
     , COUNT(DISTINCT 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`

EDIT:

However, COUNT(DISTINCT field) is more expensive then COUNT(field) and should be avoided if not necessary. Since you are not expecting it, I would say that in your case it is not necessary.

Your problem arises from the fact that your joins return 3 (comments) x 2 (categories) = 6 rows. I don't know what do you use the results for, but maybe you should re-think your query.