Select IDs in pivot table including multiple pivot ID and excluding one pivot ID

user_9486728 0
Viewed 79

How can I select the products that are in two categories and removing one category?

Example: I wish to select only the products that are in the category one and category two, excluding the category three.

A product has to have both category one and two.

If a product has the category one, two and three is excluded.

I was trying to do something like this but it doesn't work:

SELECT products.product_id , products.product_name FROM products
INNER JOIN product_category_relations ON product_category_relations.relations_product_id = products.product_id
WHERE relations_category_id IN (1,2) AND relations_category_id  NOT IN (3)
GROUP BY products.product_id

Product_id selected: 1 and 2.

Example Products Table

product_id product_name
1 tshirt
2 pants
3 Bikini
4 Jumper

Example categories Table

category_id category_name
1 category one
2 category two
3 category three
4 category four

Pivot product_category_relations Table

relations_category_id relations_product_id
1 1
2 1
4 1
1 2
2 2
1 3
2 3
3 3
1 4
4 4
having count group-by mysql sql
1 Answers

In the WHERE clause include all 3 categories and use the HAVING clause to exclude category 3:

SELECT p.product_id , p.product_name 
FROM products p INNER JOIN product_category_relations pcr
ON pcr.relations_product_id = p.product_id
WHERE pcr.relations_category_id IN (1, 2, 3) 
GROUP BY p.product_id
HAVING COUNT(*) = 2 -- only 2 categories are allowed
   AND SUM(pcr.relations_category_id = 3) = 0 -- exclude category 3

Or, simplify the HAVING clause with GROUP_CONCAT():

HAVING GROUP_CONCAT(pcr.relations_category_id ORDER BY pcr.relations_category_id) = '1,2'
user_104988280
Related