SQL Count Query

[eluser]underskor[/eluser]
I am trying to retrieve all categories, and the number of posts in each category.

Code:

SELECT COUNT(blog_entries.post_id) AS post_count,
blog_categories.category_id, blog_categories.category_title, blog_categories.category_desc
FROM blog_categories, blog_posts
WHERE blog_posts.category_id = blog_categories.category_id
GROUP BY blog_categories.category_title

This works, but it only fetches the rows of categories which have posts under them. How do I return the categories which are empty as well?

Thanks

[eluser]darkhouse[/eluser]
What you're doing now is really a JOIN, but if you change it to a LEFT JOIN you'll get the ones with no posts also.

Code:

$this->db->select('COUNT(p.post_id) post_count, c.category_id, c.category_title, c.category_desc');
$this->db->join('blog_posts p', 'p.category_id = c.category_id', 'left');
$this->db->group_by('c.category_id');
$query = $this->db->get('blog_categories c');

Should work, you might get NULL for post_count on the ones with no posts, so just display 0 in your php in that case.