[eluser]jcopling[/eluser]
Yep, you're almost there just add an additional WHERE clause to your sub-query like this:
Code:
$this->db->select("categories.*, (SELECT count(postcat_id) FROM postcats WHERE postcats.category_id = categories.category_id AND postcats.status=1) AS cat_num_posts");
I may also suggest adding a conditional to your COUNT function as your query would not return a category that 0 active posts in it.
If you are using a MSSQL DB you can do this with the ISNULL function like this:
Code:
$this->db->select("categories.*, (SELECT ISNULL(count(postcat_id),0) FROM...
or if you use MySQL you can use the IFNULL function like this:
Code:
$this->db->select("categories.*, (SELECT IFNULL(count(postcat_id),0) FROM...
That way you will return all of your categories no matter what.
Good luck!