How to count number of rows in a join but not exclude those that are not joined?

[winterain]

This is actually a very common set up but it's the first time I'm coding this myself so obviously I've ran into a limitation of my SQL knowledge.

 

I'm making a blog kinda site with posts and comments, this is for the main post listing which lists posts from table "blog_posts" . Another table "blog_comments" are linked to the "blog_posts" table via the blog_posts.ID .

 

I want to list the number of comments the post has IN THE MAIN POST LISTING page, I figured I should perform a join and count with the 2 tables, to some measurable success. However, Blog posts that do not have comments do not show up in this query. Can anyone help please? I'm not even sure if this should be the way to do it, or perhaps I'm over complicating things.

 

My current query is:

 

SELECT *, COUNT(*) AS count FROM blog_posts JOIN blog_comments ON blog_posts.ID = blog_comments.blog_id GROUP BY blog_id;

 

Thanks for your time and help!

 

 

[winterain]

Ok I've narrowed down the cause to the Group By clause...It is only showing entries that have the corresponding keys (blog_posts_ID <-> blog_comments_blog_ID ), hence blogposts that are not listed in the comments table do not show as they are not grouped...

 

not that that helps in giving me a solution though... more brainstorming... help..

[kickstart]

Hi

 

Basic problem is that you are not doing an outer join:-

 

SELECT *, COUNT(*) AS count FROM blog_posts LEFT OUTER JOIN blog_comments ON blog_posts.ID = blog_comments.blog_id GROUP BY blog_id;

 

However there are other issues with that. You will get a row (and hence a count of 1) when there are no matching blog comments, same as if there was 1 comment.

 

Also you are doing a SELECT * with a GROUP BY, which most SQL would loudly object to, while MySQL will probably cope the results will likely be unpredictable (and if you did specify all the columns in the GROUP BY the count would be useless).

 

All the best

 

Keith

[fenway]

If you COUNT(fieldName) instead of COUNT(*), you will exclude NULLs.