Relationship between product and category, build query

Question

I have a many to many relationship in mysql:

Category table:

--------------------
| id      | title |
--------------------
| 1       | cat1  |
--------------------
| 2       | cat2  |
--------------------
| 3       | cat3  |
--------------------
| 4       | cat4  |
--------------------

Product table:

--------------------
| id      | title  |
--------------------
| 1       | prod1  |
--------------------
| 2       | prod2  |
--------------------

Product category table:

--------------------------------------
| id      | id_category | id_product |
--------------------------------------
| 1       |           1 |          2 |
--------------------------------------
| 2       |           2 |          2 |
--------------------------------------
| 3       |           3 |          2 |
--------------------------------------
| 4       |           1 |          1 |
--------------------------------------
| 5       |           2 |          1 |
--------------------------------------

I want to choose products that are assigned to the category 1 or 2 and to the category 3 or 4.

In SQL, it is something like this: (category.id=1 OR category.id=2) AND (category.id=3 OR category.id=4).

In this case, I want to select only the product 2.

How do I construct a SQL query?

Answer 1

Try the query below:

SELECT *
 FROM product_category pc
WHERE
 (pc.id_category=1 OR pc.id_category=2)
AND EXISTS
(SELECT *
 FROM product_category pc2
WHERE
 pc2.id_product = pc.id_product
AND
 (pc2.id_category=3 OR pc2.id_category=4)
)