select all fields plus total children

[brian ally]

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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]