MySQL: Get products tha only have disabled categories-CodePudding

A product can have multiple categories associated via the pivot table products_categories.
The categories can be enabled (status = 1) or disabled (status = 0).
The id column in the categories table is unique / auto_increment.

I need to find products that ONLY have disabled categories.

So if a product belongs to two categories, one with status = 0 and one with status = 1 that products should not be part of my result.

Obviously this will not work:

SELECT
    products.id
FROM
    products
JOIN
    products_categories
ON
    products.id = products_categories.product_id
JOIN
    categories
ON
    products_categories.category_id = categories.id
WHERE
    categories.status = 0
;

Here is my tables:

products
id (AUTO_INCREMENT)

products_categories
product_id  (INT)
category_id (INT)

categories
id     (AUTO_INCREMENT)
status (INT)

CodePudding user response：

If you need only the product ids then you can use only the tables products_categories and categories.

With NOT EXISTS:

SELECT pc.product_id
FROM products_categories pc 
WHERE NOT EXISTS (
  SELECT *
  FROM categories c
  WHERE c.id = pc.category_id AND c.status = 1
);

or, with aggregation:

SELECT pc.product_id
FROM products_categories pc INNER JOIN categories c
ON c.id = pc.category_id
GROUP BY pc.product_id
HAVING MAX(c.status) = 0;

CodePudding user response：

In this case GROUP BY products.id with HAVING MIN(status) = '0' AND MAX(status) = '0' should do the trick.

SELECT products.id
FROM products
JOIN products_categories ON products.id = products_categories.product_id
JOIN categories ON products_categories.category_id = categories.id
GROUP BY products.id
HAVING MIN(status) = '0' AND MAX(status) = '0';

In this fiddle you can find a simple demo.