0

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 taco and burrito are in the mexican category. hamburger is in the american category. And pepperoni pizza is in the pizza category which is a subcategory of italian.

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.