CODEWITHSUNDEEP
sql
Sae Us
Sae Us

Reputation: 277

SQL Where IN but match all

I've been searching around on the web and SO and can't find a straight forward answer to this issue - it seems like something really really obvious but I'm having no luck.

I have a SQL query which has the line:

WHERE Genre IN ('Rock', 'Popular', 'Classical')

However what this does is give a long list of results, namely does a OR however what i want instead are results that match all 3.

Thanks.

Upvotes: 2

Views: 493

Answers (3)

Barmar
Barmar

Reputation: 781380

SELECT id, COUNT(*) c
FROM table
WHERE Genre IN ('Rock', 'Popular', 'Classical')
GROUP BY id
HAVING c = 3

This assumes the combinations id, Genre are unique, which they should be in a properly normalized schema.

Upvotes: 1

sgeddes
sgeddes

Reputation: 62831

Here's an alternative approach, but I like Gordon's approach as well:

SELECT id
FROM yourtable
WHERE genre IN ('Rock', 'Popular', 'Classical')
GROUP BY id
HAVING COUNT(DISTINCT Genre) = 3

This counts the distinct genres associated with an id and only returns the ones that match all 3.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270091

This is a "set-within-sets" query. I advocate using aggregation with a having clause.

Here is an approximation of such a query:

select id
from t
group by id
having sum(case when genre = 'Rock' then 1 else 0 end) > 0 and      -- has Rock
       sum(case when genre = 'Popular' then 1 else 0 end) > 0 and   -- has Popular
       sum(case when genre = 'Classical' then 1 else 0 end) > 0     -- has Classical

The having clause may look a little complicated. But each clause is just counting the number of rows that match one of the values. If there are any, then that genre passes that test.

Upvotes: 1

Related Questions