I have 2 tables, category & product. product contains category_id which points back to category.id
I'm trying to get all fields for each category plus the total no. of products in that category. A simple query for this:
SELECT c.*, COUNT(p.id) AS total FROM category AS c, product AS p WHERE c.parent_id = 0 AND p.category_id = c.id GROUP BY c.name
unfortunately will not show any categories which currently have no products assigned. I'm sure i need a join in there but haven't found it.
I'm also sure i'll slap my forehead when i see the proer way to do this. Any help appreciated.
brian
Brian, You need to do a Left Join as in:
SELECT c.*, sum(1-ISNULL(p.id)) AS total FROM category AS c left join product AS p on c.id=p.category_id WHERE c.parent_id = 0 GROUP BY c.name
The left join will join the tables and if there is no corresponding row on the right, it fills it with null.
The sum(1-ISNULL(p.id)) will sum all occurrences of p.id that are not null as 1, and sums 0 if they are null. You need to do that because p.id is NULL when there is no corresponding product row.
Ok, slap away! <g>
Mike
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
