Search code examples
phpmysqlrelational-division

MYSQL: Find rows where multiple ID match

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?

Solution

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