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.