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 |