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

Answer 1

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 */