CodeIgniter Forums
SQL Count Query - Printable Version

+- CodeIgniter Forums (
+-- Forum: Archived Discussions (
+--- Forum: Archived Development & Programming (
+--- Thread: SQL Count Query (/showthread.php?tid=14714)

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

I am trying to retrieve all categories, and the number of posts in each category.
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?


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

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.

$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');
$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.