strange sql query implementation joining tables

Question

I need some help with an SQL query. Let me describe the scenario

I have three tables, dispensaries, goods and products.

products records has two fk, good_id and dispensary_id that let me access from dispensaries to goods through products. Also goods has an attribute called name

Now the situation i wanna resolve is the following:

For a provided set of goods names ([good_name1, good_name2, etc]) i want to get all the dispensaries that has ALL the goods in the array ( the goods that matches with those names ).

Let me add an example:

 good1 whose names is  good_1 belongs to dispensary1
 good1 whose names is  good_1 belongs to dispensary2
 good2 whose names is  good_2 belongs to dispensary1

so i need to create an SQL query that with a provided array of goods [good1, good2] returns the only the dispesary1

Thanks in advance.

score 0 · Answer 1 · edited May 23 '17 at 12:24

You will probably want to turn this into a function that accepts an array of the goods.name items that you are after. First I would get the syntax and format of the data correct though. Try the following from a query window to see if it gets you the data you want with a static list of goods.name text items.

SELECT dispensary.id, dispensary.name
FROM products
LEFT JOIN goods ON goods.id = products.goods_id
LEFT JOIN dispensary ON dispensary.id = products.dispensary_id
WHERE goods.name IN ([good_name1, good_name2, etc])
AND dispensary.id IS NOT NULL;

EDIT

To return only the dispensaries with ALL items in the array try (modified reference from @ErwinBrandstetter answer PostgreSQL where all in array):

CREATE OR REPLACE FUNCTION f_dispensaries_with_all_products(_user_arr string[])
  RETURNS SETOF dispensary AS
$BODY$
DECLARE
    _sql text := '
    SELECT d.*
    FROM   products p
    JOIN   dispensary d ON d.id = p.dispensary_id';
    i int;
BEGIN

FOREACH i IN ARRAY _user_arr LOOP
    _sql  := _sql  || '
    JOIN   goods g' || i || ' ON g|| i || '.id = p.good_id';
END LOOP;

_sql  := _sql  || '
    WHERE  d.id IS NOT NULL ';

FOREACH i IN ARRAY _user_arr LOOP
    _sql  := _sql  || '
    AND    g' || i || '.name = ' || _user_arr[i];
END LOOP;

-- RAISE NOTICE '%', _sql;
RETURN QUERY EXECUTE _sql;

END;
$BODY$ LANGUAGE plpgsql VOLATILE;

Not what i need, check that IN clause just give me all the dispensaries that has a good that is inside the array i'm providing it, but i need that all the goods from the array to belongs to the dispensary in order to return it — Guillermo kuster, Jun 07 '16 at 00:07

score 0 · Answer 2 · answered Jun 07 '16 at 02:02

One way to achieve that is to use INTERSECT, generating a set for each of the clauses — which means you would have to unfold the list into n queries, one for each item:

SELECT dispensary.id, dispensary.name
FROM products
INNER JOIN goods ON goods.id = products.goods_id
INNER JOIN dispensary ON dispensary.id = products.dispensary_id
WHERE goods.name = 'good_1'
INTERSECT
SELECT dispensary.id, dispensary.name
FROM products
INNER JOIN goods ON goods.id = products.goods_id
INNER JOIN dispensary ON dispensary.id = products.dispensary_id
WHERE goods.name = 'good_2'
INTERSECT
SELECT dispensary.id, dispensary.name
FROM products
INNER JOIN goods ON goods.id = products.goods_id
INNER JOIN dispensary ON dispensary.id = products.dispensary_id
WHERE goods.name = 'good_3'

That way, you'll only get results that are in all sets.

strange sql query implementation joining tables

2 Answers2