How to solve this relational division query?

Question

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

score 6 · Accepted Answer · answered May 29 '13 at 01:07

6

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

SQL of Relational Division

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

answered May 29 '13 at 01:07

John Woo

258,903
69
498
492

Really thanks men, it worked fine. I wasn't figuring how to solve it – Fosfor May 29 '13 at 01:49
@JW웃 Could you explain more please about how the "having count(*) = 3" works? What if there were only 2 genre_ids? 5? Would you change the having count clause to = 2 or 5 respectively? I noticed also that if I don't have the having count clause the where in clause acts like a regular OR clause for each of the IDs. – user658182 Jul 01 '13 at 13:31

score 2 · Answer 2 · answered May 29 '13 at 01:09

This is an example of a "set-within-sets" query. I think the most general way is to put all the conditions in the having clause:

SELECT movie_id
FROM movies
group by movie_id
having sum(case when genre_id = 1 then 1 else 0 end) > 0 and
       sum(case when genre_id = 2 then 1 else 0 end) > 0 and
       sum(case when genre_id = 3 then 1 else 0 end) > 0;

Each of the clauses in the having clause is counting the number of records that match each type of genre.

How to solve this relational division query?

2 Answers2