MySQL: Get products tha only have disabled categories

Issue

  • 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)

Solution

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;

Answered By – forpas

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published