2

I have a table setup similarly as below.

genre_id  series_id
1         4
1         2
2         5
4         1
2         4
3         3

What I want to do is to be able to find all series based on the mix of genres selected.

For example finding all series that have a genre id of 1 and 2. Hypothetically the result I want is the series ID of 4.

If I use 

SELECT series_id FROM table WHERE genre_id = 1 AND genre_id = 2

it returns nothing.

If I use

SELECT series_id FROM table WHERE genre_id in (1, 2)

it returns everything in 1 and 2. But I just want the rows where the genre_id's intersect.

Is there any way to do this?

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
RWW
  • 49
  • 2
  • 6

2 Answers2

4

This should do the trick:

SELECT series_id FROM table
WHERE genre_id IN (1, 2)
GROUP BY series_id
HAVING COUNT(*) = 2

Note this is assuming that the pair (genre_id, series_id) is unique. If it is not you will have to change the HAVING clause to

HAVING COUNT(DISTINCT genre_id) = 2

Also note that the number 2 in the HAVING clause must match the amount of items in the IN clause.

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
2

You can think of the IN() predicate as basically a series of OR terms; it's equivalent to

SELECT series_id
FROM MyTable 
WHERE genre_id = 1 OR genre_id = 2

What you want is to turn the OR into AND, but that doesn't make any sense because a WHERE expression applies to one row at a time. There's no way genre_id can be both 1 and 2 on the same row.

So you need to compare genre_id from two different rows in one expression. You can do this by joining the two rows:

SELECT t1.series_id
FROM MyTable AS t1
INNER JOIN MyTable AS t2 USING (series_id)
WHERE t1.genre_id = 1 AND t2.genre_id = 2

There's also a solution using GROUP BY as shown in another answer, but the self-join can be orders of magnitude more efficient if you define the right indexes.

I describe more details for this solution in my presentation SQL Query Patterns, Optimized.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • So if I had lets say n genre_ids to match for series_ids, would there be an elegant way of doing it, instead of doing n amount of joins? – RWW Dec 03 '13 at 01:40
  • You'll need n joins. The more efficient way of achieving a query isn't necessarily the one that is written in the fewest characters. – Bill Karwin Dec 03 '13 at 01:43