I have a list of products, and a list of categories, and a join table. Products can be assigned to multiple categories. Categories can have one level of subcategories. The tables look something like this:
products ========= id | name ========= 1 | taco 2 | hamburger 3 | burrito 4 | pepperoni pizza categories ===================== id | name | parent_id ===================== 1 | American | NULL 2 | Italian | NULL 3 | Pizza | 2 4 | Chinese | NULL 5 | Mexican | NULL category_product ======================== category_id | product_id ======================== 5 | 1 1 | 2 5 | 3 3 | 4
So you can see that
burrito are in the
hamburger is in the
american category. And
pepperoni pizza is in the
pizza category which is a subcategory of
I'm trying to get a list of ALL categories and subcategories along with how many products are in each category and subcategory.
I thought this would do it:
SELECT c.name, c.id, c.parent_id count(p.id) AS num_products FROM products AS p CROSS JOIN categories AS c LEFT JOIN category_product AS cp ON cp.category_id = c.id AND cp.product_id = p.id GROUP BY c.id ORDER BY c.parent_id, c.name
This properly returns a list of all categories and subcategories (even categories that are not assigned, which is how I want it). But the
num_products field is
4 for every row, which not-coincidentally is the total number of products.
Alternatively I can do something like this:
SELECT c.name, c.id, c.parent_id, count(p.id) AS num_products FROM categories AS c LEFT JOIN category_product AS cp ON c.id = cp.category_id LEFT JOIN products AS p on p.id = cp.product_id GROUP BY c.id ORDER BY c.parent_id,c.name;
Which does return the correct number of products in each category, however it doesn't include all the categories that don't have any products AND it doesn't return the parent categories of subcategories that have products.
Can either of the above queries be altered to give what I want? Can the first query be adjusted to return the correct number of products per category? Or can the second query be adjusted to also return all categories and subcategories?