SQL Query on a multiple field value

Question

I spent hours looking for that answer but I think I might not be looking in the right direction. Here is my problem:

Let's say I have a simple table like this:

entity_id    delta    option_id
-------------------------------
1            0        10
1            1        11
2            0        10
2            1        11
2            2        12

I have a multi value field and I want to find the the that has the option_id (10,11,12), it should returns entity_id = 2. I have tried using IN, but it is not 'precise' enough, I mean that I can search option_id (11,12), it will still return me entity_id 2, which is not what I want. I have tried with ALL, but I don't think it is what I want. Thanks a lot for your help. I am pretty sure the answer is in someone else post but I haven't been able to find it.

Ed

Joe Stefanelli · Accepted Answer · 2015-11-19 15:38:57Z

3

You use a GROUP BY and HAVING here to find the element with all the items you're looking for.

SELECT t.entity_id
    FROM your_table t
    WHERE t.option_id IN (10,11,12)
    GROUP BY t.entity_id
    HAVING COUNT(DISTINCT t.option_id) = 3; /* because there are 3 elements in the IN clause */

answered Nov 19, 2015 at 15:38

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

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

2 Comments

Doud Over a year ago

I will try that now, and see if I manage to make this work. Thanks a lot!

Doud Over a year ago

Works like a charm ! Thanks you so much !

Collectives™ on Stack Overflow

SQL Query on a multiple field value

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related