- 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)
If you need only the product ids then you can use only the tables
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