CODEWITHSUNDEEP
sqlsql-server
Charles Khunt
Charles Khunt

Reputation: 2505

Alternative to INTERSECT Given Arbitrary Number of conditions

If I have a table similar to the following:

MyIds MyValues
----- --------
  1    Meat
  1    Fruit
  1    Veggies
  2    Fruit
  2    Meat
  3    Meat

How do I create a query such that if I am given an arbitrary list of distinct MyValues, it will give me all the MyIds that match all of MyValues.

Example: If list of MyValues contained [Meat, Fruit, Veggies], I'd like to get MyIds of 1 back because 1 has an entry in the table for each value in MyValues.

I know that this can be done with an INTERSECT if I'm given a specific list of MyValues. But I don't know how it can be done with an arbitrary number of MyValues

Upvotes: 1

Views: 123

Answers (2)

John Woo
John Woo

Reputation: 263703

You need to count the total number of instances of each MyID which satisfies the condition and that it matches to the number of value supplied in the IN clause.

SELECT MyID
FROM tableName
WHERE MyValues IN ('Meat', 'Fruit', 'Veggies')
GROUP BY MyID
HAVING COUNT(DISTINCT myVAlues) = 3

SQLFiddle Demo

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269593

A big question is how the list is being represented. The following gives one approach, representing the list in a table:

with l as ( 
    select 'Meat' as el union all
    select 'Fruit' union all
    select 'Veggies'
)
select MyId
from t join
     l
     on t.MyValues = l.el
group by MyId
having count(distinct t.myvalues) = (select count(*) from l)

Upvotes: 0

Related Questions