PSQL: select all assets that have all of given list of associated objects ids

Question

Suppose we have a standard has and belongs to many association between products and categories product_categories table consists of product_id and category_id pairs.

We have list of category_id's and need to select all products that belongs to all these categories.

The best approach that came to my mind is to have multiple EXISTS() in where clause conjuncted by AND at programming language level:

SELECT * FROM products p WHERE 
EXISTS(SELECT * FROM product_categories pc 
   WHERE pc.product_id = p.id AND pc.category_id = {first_id}) AND
EXISTS(SELECT * FROM product_categories pc 
   WHERE pc.product_id = p.id AND pc.category_id = {second_id}) AND ...

EXISTS is fast in Postgres 8.4 so no performance issue with this approach.

But I wounder is there any more elegant of doing this type of queries?

Answer 1

SELECT * FROM products p WHERE 
EXISTS(SELECT * FROM product_categories pc 
       WHERE pc.product_id = p.id AND pc.category_id = {first_id}) 
INTERSECT
SELECT * FROM products p WHERE 
EXISTS(SELECT * FROM product_categories pc 
       WHERE pc.product_id = p.id AND pc.category_id = {second_id}) 
INTERSECT
....