PostgreSQL WHERE IN clause variation

Question

Fictional example

SELECT products_campaign.id
FROM products_campaign 
INNER JOIN products_store ON products_campaign.id_campaign = products_store.id_campaign 
WHERE products_store.id_store in (100, 200, 300)
GROUP BY id

This brings me every product id that is in store 100, 200 OR 300.
I want to build a query that gives me every product id that is in store 100, 200 AND 300, that is, the product has to exist in all three stores (intersect).

Answer 1

You can use array_agg() with the operator@>:

SELECT products_campaign.id
FROM products_campaign 
INNER JOIN (
    SELECT id_campaign
    FROM products_store 
    GROUP BY id_campaign
    HAVING (array_agg(id_store) @> array[100, 200, 300])
    ) products_store
ON products_campaign.id_campaign = products_store.id_campaign 
GROUP BY id

Read about array operators.