Search code examples
mysqlsqlrelational-division

How to solve this relational division query?

I have a table (movies) with two foreign keys, (movie_id, genre_id). Of course, both fields aren't unique.

How do I select the movies that have two or more specific genres?

I want to do something like:

SELECT movie_id 
FROM movies
WHERE genre_id = 1 AND genre_id = 2 AND genre_id = 3
Solution

  • This is a Relational Division problem.

    SELECT movie_id 
FROM   movies
WHERE  genre_id IN (1, 2, 3)   -- <<== list of genre_id
GROUP  BY movie_id
HAVING COUNT(*) = 3            -- <<== count of genre_id

    If genre_id is not unique for every movie_id, a DISTINCT keyword is needed to filter unique ones.

    SELECT movie_id 
FROM   movies
WHERE  genre_id IN (1, 2, 3)             -- <<== list of genre_id
GROUP  BY movie_id
HAVING COUNT(DISTINCT genre_id) = 3      -- <<== count of genre_id