Newbie question : N-N join that matches ALL a list

Tables :

-- products --
   id

-- categories --
   id

-- products_categories --
   id
   product_id
   category_id

What could be a SQL query that returns all th开发者_开发知识库e products IDs that matches ALL a given list of category_ids ?

Example : Given the list (3, 4, 5) I would like all product_ids that have AT LEAST (could be more) the category id 3 and the category id 4 and the category id 5 ?

Use:

  SELECT p.id
    FROM PRODUCTS p
    JOIN PRODUCTS_CATEGORIES pc ON pc.product_id = p.id
    JOIN CATEGORIES c ON c.id = pc.category_id
   WHERE c.id IN (3,4,5)
GROUP BY p.id
  HAVING COUNT(DISTINCT c.id) = 3

This is popularly known as Celko's division.

The COUNT(DISTINCT c.id) must equal the number of values specified in the IN clause. Otherwise, duplicates of 4/3/5/etc would be false positives. However, if all the pairs of product_id, category_id are guaranteed to be unique, DISTINCT can be omitted.

Another option that can be read as a double negative:

Show all products for which there is no category in (3,4,5) for which there is no match with that product.

SELECT p.id
FROM PRODUCTS p
WHERE NOT EXISTS
      ( SELECT *
        FROM CATEGORIES c 
        WHERE c.id IN (3,4,5)
          AND NOT EXISTS
              ( SELECT *
                FROM PRODUCTS_CATEGORIES pc
                WHERE pc.product_id = p.id
                  AND pc.category_id = c.id
              )
      )

This is popularly known as Date's division.