sql - Postgres array query -

(the following highly simplified description of problem. company policy not allow me describe actual scenario in detail.)

the db tables involved are:

products: id   name --------- 1    ferrari 2    lamborghini 3    volvo   categories: id    name ---------- 10    sports cars 20    safe cars 30    red cars  products_categories productid    categoryid ----------------------- 1            10 1            30 2            10 3            20  locations: id      name ------------ 100     sports car store 200     safe car store 300     red car store 400     cars r   locations_categories: locationid    categoryid ------------------------ 100           10 200           20 300           30 400           10 400           20 400           30 

note locations not directly connected products, categories. customer should able see list of locations can provide product categories products want buy belong to. so, example:

a customer wants buy ferrari. available stores in categories 10 or 30. gives stores 100, 300 , 400 not 200.

however, if customer wants buy volvo , lamborghini available stores in categories 10 and 20. gives store 400.

another customer wants buy ferrari , volvo. store in either categories 10 + 20 (sporty , safe) or categories 30 + 20 (red , safe).

what need postgres query takes number of products , returns locations of them can found. got started arrays , <@ operator got lost quickly. here follows example sql attempts stores ferrari , lamborghini can bought. not work correctly since requires locations satisfy all categories all selected cars belong to. returns location 400 should return locations 400 , 100.

select l.* locations l  (select array_agg(distinct(categoryid)) products_categories productid in (1,2)) <@ (select array_agg(categoryid) locations_categories locationid = l.id); 

i hope description makes sense.

here query. should insert list of selected cars ids pc.productid in (1,3) , in end should correct condition selected cars count if select 1 , 3 should write having count(distinct pc.productid) = 2 if select 3 cars there have 3. condition in having give condition cars in these locations:

select id locations l join locations_categories lc on l.id=lc.locationid join products_categories pc on lc.categoryid=pc.categoryid pc.productid in (1,3) group l.id having count(distinct pc.productid) = 2 

sqlfiddle demo

for example 1 car be:

select id locations l join locations_categories lc on l.id=lc.locationid join products_categories pc on lc.categoryid=pc.categoryid pc.productid in (1) group l.id having count(distinct pc.productid) = 1 

only ferrary demo volvo , lamborghini demo