Listing list of posts with comment count for each post. SQL Query 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