query exact match in an array-CodePudding

items :

id | name  | ref
---|-------|----
1  |item 1 | x2
2  |item 2 | x3

options:

id | option_id | item_id 
---|-----------|--------
1  | 1         | 1
2  | 2         | 1
2  | 3         | 1
2  | 1         | 2
2  | 3         | 2

given item ref = 'x2' and array of options [1,2,3] I should get 1 row of item 1 with exact options. given item 1 with options [1,2] should return null.

I tried this but could not get the result:

  SELECT items.*,(
    SELECT  options.id
    FROM    options, items
    WHERE   options.option_id IN (1,2,3) AND options.item_id = items.id 
   GROUP BY options.id, items.id
   HAVING COUNT(options.*) = 2)
   FROM items WHERE items.ref = 'x2';

what will be the best options?

CodePudding user response：

here is one way by making an array of options and compare them :

select * from items i 
join ( select item_id , ARRAY_AGG(option_id order by option_id) optionids
       from options 
       group by item_id
) o  on i.id = o.item_id
where ref = 'x2'
and optionids <@ array[1,3,2]

db<>fiddle here