Mysql: know if all the products within a category are inactive

Question

everybody,

I have a table in my MYSQL 5.7 database that links categories and products in this way:

category_product

id_category | id_product
10 | 23
10 | 34
10 | 51
11 | 74
11 | 95
...

And the product table looks like this:

id_product | name | active
23 | ball | 1
34 | shoes | 1
51 | map | 0
74 | shirt | 0
95 | ring | 0

The "active" field is used to find out whether the product is active for sale or not.

I need to know if a category has all the products that are within it inactive (active=0). This is the query I have come to but it doesn't work... I don't know MYSQL well yet:

        SELECT 
        cp.id_category
        FROM category_product cp
        INNER JOIN product p ON cp.id_product = p.id_product
        where p.active=0 and cp.id_category=10
        GROUP BY cp.id_category

It doesn't work because it doesn't do what I need it to: it doesn't show me if the category with id 10 has all the products within it active=0. And ideally I shouldn't have to supply with the id_category in WHERE clause but if necessary I can do that.

Thank you very much for your help, I've been doing this for a long time and I have no more ideas

Answer 1

Just add a HAVING clause which asserts that the sum of active flags is zero:

SELECT cp.id_category
FROM category_product cp
INNER JOIN product p ON cp.id_product = p.id_product
WHERE cp.id_category = 10
GROUP BY cp.id_category
HAVING SUM(pt.active) = 0;