Select entities using multiple criteria from many-to-many association entity

I’m having trouble selecting entities of type Product where the product has two or more Categories.

I have following tables:

product
============
product_id (PK)
name

category
============
category_id (PK)
name

and their association:

product_category
============
product_id (PFK)
category_id (PFK)

Now the issue is, that I’m not able to select product according to multiple category.category_id’s using product_category table.

For example: I want to select all products which both category 1, 2, that means that there are two occurrences in table product_category.

To select all products with single category a following select can be used

SELECT p.*
FROM product p
JOIN product_category pc using(product_id)
WHERE category_id = 1;

But how can I select where the product has category_id 1 and 2? The query should return only products where both categories are associated.

WHERE category_id = 1 and category_id = 2;

This is obviously impossible match, but how can I workaround this? Is that even possible?

Answer

Join a derived table in which you filter all associations for being of either of the wanted categories. Then group by the product and use a HAVING clause, that checks for the distinct count of categories being equal to the number of wanted categories. A product having all wanted categories will satisfy this.

SELECT p.*
       FROM product p
            INNER JOIN (SELECT pc.product_id
                               FROM product_category pc
                               WHERE pc.category_id IN (1, 2)
                               GROUP BY pc.product_id
                               HAVING count(DISTINCT pc.category_id) = 2)
                       ON pc.product_id = p.product_id;