query exact match in an array

Question

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?

Answers to this SO question may give you an idea.

Stefanov.sm
– Stefanov.sm

2021-10-08 12:49:33 +00:00
Commented Oct 8, 2021 at 12:49 — Stefanov.sm
– Stefanov.sm, Commented Oct 8, 2021 at 12:49

eshirvana · Accepted Answer · 2021-10-08 16:03:36Z

1

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

edited Oct 8, 2021 at 16:03

answered Oct 8, 2021 at 13:58

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chosen Over a year ago

is there any way that i can explicitly select the item with the same reference lets say there is item3 with ref 'x2' but different options [2,3]. the code above shows me 2 rows. i need only 'x2' and options [1,2,3] "exactly"?

Chosen Over a year ago

i got it , i just changed the "<@" to "=" and added another criteria orders.name = 'item 1', as i needed it. thanks @eshirvana

Collectives™ on Stack Overflow

query exact match in an array

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related