MySQL连接和COUNT（）不返回所有记录

Question

I have two database tables: "blog_posts" and "blog_comments".

blog_posts:

postID | postTitle   | postContent               | ...  
1      | Hello World | This is a blog post       | ...  
2      | Lorem Ipsum | This is another blog post | ...  
3      | Test Post   | This is a third post      | ...

blog_comments:

commentID | postID | comment   | ...  
1         | 1      | Very cool | ...  
2         | 1      | Nice      | ..

My current sql query is:

SELECT
  blog_posts.*,
  COUNT(blog_comments.commentID) AS commentCount
FROM
  blog_posts
  LEFT JOIN blog_comments ON blog_posts.postID = blog_comments.post_id;

I want it to return 0 as commentCount if there are no comments to this post, but instead the query returns only the first post which is the only one which has comments at the moment.

How can I fix that?

Thanks.

Answer 1

You're using COUNT without specified grouping column which means all rows are agregated into one and count is evaluated on the whole set.

Specify grouping column explicitely and you should be fine:

SELECT
  blog_posts.*,
  COUNT(blog_comments.commentID) AS commentCount
FROM
  blog_posts
  LEFT JOIN blog_comments ON blog_posts.postID = blog_comments.post_id
GROUP BY blog_posts.postID;