unflyingobject

So let's say you're fetching posts from your blog app and also want to show the comment count for every post. You can't use a typical JOIN because some posts may not have any comments at all and doing a separate query in the comments table is just plain wasteful. Use a LEFT OUTER JOIN instead:

SELECT *, COUNT(comments.id) AS comment_count

FROM posts LEFT OUTER JOIN comments ON (posts.id = comments.post_id)

GROUP BY posts.id

ORDER BY date_added DESC

LIMIT 0, 20