i need sql query. let me describe scenario
i have 3 tables, dispensaries, goods , products.
products records has 2 fk, good_id , dispensary_id let me access dispensaries goods through products. goods has attribute called name
now situation wanna resolve following:
for provided set of goods names ([good_name1, good_name2, etc]) want dispensaries has goods in array ( goods matches names ).
let me add example:
good1 names good_1 belongs dispensary1 good1 names good_1 belongs dispensary2 good2 names good_2 belongs dispensary1 so need create sql query provided array of goods [good1, good2] returns dispesary1
thanks in advance.
one way achieve use intersect, generating set each of clauses — means have unfold list n queries, 1 each item:
select dispensary.id, dispensary.name products inner join goods on goods.id = products.goods_id inner join dispensary on dispensary.id = products.dispensary_id goods.name = 'good_1' intersect select dispensary.id, dispensary.name products inner join goods on goods.id = products.goods_id inner join dispensary on dispensary.id = products.dispensary_id goods.name = 'good_2' intersect select dispensary.id, dispensary.name products inner join goods on goods.id = products.goods_id inner join dispensary on dispensary.id = products.dispensary_id goods.name = 'good_3' that way, you'll results in sets.
