Listing list of posts with comment count for each post. SQL Query help?

[eluser]winterain[/eluser]
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'm trying 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.

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;

I realize that it is mainly because of the GROUP BY clause that causes posts that have no matching IDs to be excluded from the goup, but that only makes me more clueless on how to solve this.

Thanks for your time and help!

[eluser]umefarooq[/eluser]
a simple solution is sub query

Code:

select a.*,(select count(*) from blog_comments where blog_id = a.id ) as count from blog_posts as a

hope this will help you to get your desire result if there is no matching id than count will be 0

[eluser]xwero[/eluser]
the problem with this sql statement is that it extracts more data than needed. the count value will be added to each row.
the solution is to split up the query.

[eluser]winterain[/eluser]
I'm a little unsure of how to go about implementing a separate query for this, maybe I'm thinking in the wrong direction. Just to show what I'm trying to accomplish more clearly, my page basically looks like this:

Blog Posts

Post title A
Written 2009-01-01 (2 comments)

[Excerpt]

Post title B
Written 2009-01-01 (5 comments)

[Excerpt]

Post title C
Written 2009-01-01 (1 comments)

[Excerpt]

and so on...

I only decided to add in the no. of comments thingy after I finished everything, so I think I might be looking at achieving it the wrong way.

Thing is if I split the queries into a list_posts and list_comments_number, I wouldn't be able to combine the listing like how it should look like..

Can anyone enlighten me please?

[eluser]winterain[/eluser]
I knew I had my head stuck in a hole somewhere.. problem is fixed.

All i had to do was to call the countComments function from the model in the loop when the posts are populated!

I had mistakenly assumed that the functions in the model are only for within the controllers.