CODEWITHSUNDEEP
postgresqlselectwhere-in
Edson Horacio Junior
Edson Horacio Junior

Reputation: 3143

PostgreSQL WHERE IN clause variation

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).

Upvotes: 0

Views: 146

Answers (3)

klin
klin

Reputation: 121604

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.

Upvotes: 1

mechanical_meat
mechanical_meat

Reputation: 169304

SELECT products_campaign.id
FROM products_campaign 
INNER JOIN (
     SELECT pc.id, count(distinct ps.id_store)
     FROM products_campaign pc
     INNER JOIN products_store ps 
     ON pc.id_campaign = ps.id_campaign 
     AND ps.id_store in (100,200,300)
     GROUP BY pc.id
     HAVING count(distinct ps.id_store) = 3
) intersection
ON intersection.id = products_campaign.id 
INNER JOIN products_store 
ON products_campaign.id_campaign = products_store.id_campaign 
GROUP BY products_campaign.id

Upvotes: 1

e4c5
e4c5

Reputation: 53734

This should do the trick.

SELECT products_campaign.id
FROM products_campaign 
 INNER JOIN (SELECT * FROM products_store WHERE id_store = 100) AS a 
   ON products_campaign.id_campaign = a.id_campaign
 INNER JOIN (SELECT * FROM products_store WHERE id_store = 200) AS b 
   ON products_campaign.id_campaign = a.id_campaign

 INNER JOIN (SELECT * FROM products_store WHERE id_store = 300) AS c 
   ON products_campaign.id_campaign = a.id_campaign

GROUP BY id

If it doesn't, please post your create table statements, and show what you expect as output

Upvotes: 1

Related Questions