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

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

Answer

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'