MYSQL: Find rows where multiple ID match

Question

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?

Answer 1

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.

Answer 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.