SQL Count Query - Printable Version

SQL Count Query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: SQL Count Query (/showthread.php?tid=14714)

SQL Count Query - El Forum - 01-12-2009

[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

SQL Count Query - El Forum - 01-12-2009

[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.