sql finding movie(s) with specific genres

Question

select 
  film.title
from film
  left outer join filmitem ON film.filmid = filmitem.filmid
  left outer join filmgenre ON filmitem.filmid = filmgenre.filmid
where
  film.title = title and filmgenre.genre NOT IN (
    select genre from genre
    where genre != 'Comedy' and genre != 'Horror')
group by title;

I want to find movies that only contains the genres 'Comedy' and 'Horror'. When I run this query I get movies that are comedy and other genders, but I want to exclude the other genders and only get the movies that are only comedy and horror. Any suggestions please?

Surely if you want to match one of "comedy" or "horror" then you just need "... And genre in ('Comedy', 'Horror')" — kaj, Feb 15 '12 at 13:27
@JohnSmith: Will there always be at least one genre assigned to a film? — , Feb 15 '12 at 13:36

Sergey Kudriavtsev · Accepted Answer · 2012-02-15T13:36:23.037

5

select 
  film.title
from film
  left outer join filmitem ON film.filmid = filmitem.filmid
  left outer join filmgenre ON filmitem.filmid = filmgenre.filmid
where
  film.title = title and filmgenre.genre IN ('Comedy', 'Horror')
group by title;

UPD: Argh, sorry, read your question second time. Will update the query in a minute...

UPD2: If you need to select films that have only both genres 'Comedy' and 'Horror' then you can use

select 
  film.title
from film
  left outer join filmitem ON film.filmid = filmitem.filmid
where
  film.title = title
  AND EXISTS (SELECT * FROM filmgenre fg WHERE filmitem.filmid = fg.filmid AND fg.genre='Comedy')
  AND EXISTS (SELECT * FROM filmgenre fg WHERE filmitem.filmid = fg.filmid AND fg.genre='Horror')
  AND NOT EXISTS (SELECT * FROM filmgenre fg WHERE filmitem.filmid = fg.filmid AND fg.genre NOT IN ('Comedy','Horror'))
group by title;

Although I'm pretty sure that there's more efficient way to do it...

edited Feb 15 '12 at 13:36

answered Feb 15 '12 at 13:24

Sergey Kudriavtsev

10,328
4
43
68

This is exactly what you want. If you're selectign muultiple fields (such as id) i would make a suggestion to do as Sergey did and append the table name prefix before the column IE: film.id, genre.id, film.title – NDBoost Feb 15 '12 at 13:29
1

@Mike: If I understand OP correctly, he needs to select films that have _only_ 'Comedy' and 'Horror' and omit films that are, say, 'Comedy' , 'Horror' and 'Adventure'. That will require somewhat more complex query. – Sergey Kudriavtsev Feb 15 '12 at 13:31
ahh i missed that as well. @OP how are you storing the list of Genres? If its a comma delimited i'd think IN would still work, no? – NDBoost Feb 15 '12 at 13:35
1

As you said, there is a more efficient way of doing it. This is best acheived using `GROUP BY` AND `HAVING`, this can perform the filtering no matter how many genres are required. For just 2 genres the number of table scans performed on "FilmGenre" rises from the required 1 to 3, if this was required for only films that were in 4 genres using the logic applied here would result in 5 `EXISTS` clauses, performing 5 times the number of reads than necessary. – GarethD Feb 15 '12 at 13:58
@GarethD: True. I do like your approach more, although you need to correct "f.FileID" to "g.FilmID". – Sergey Kudriavtsev Feb 15 '12 at 14:04
@JohnSmith: Thanks for accepting, but I'd suggest using GarethD's solution instead - it's really more efficient. – Sergey Kudriavtsev Feb 15 '12 at 14:05
@SergeyKudriavtsev Doh! replaced f with g. Thanks. – GarethD Feb 15 '12 at 15:57
When i try to change SELECT film.title to SELECT COUNT(film.title), the query just prints 1 for each film, instead of counting them. Why does it happen? – user265767 Mar 05 '13 at 16:12
1

@user265767: Because of `GROUP BY` clause. If `title` is unique in scope of `films` table, query shall work just well without `GROUP BY`, so I'd suggest removing it and trying again. – Sergey Kudriavtsev Mar 05 '13 at 16:47

score 3 · Answer 2 · edited May 23 '17 at 12:23

If you only need film title the below should offer the best performance. There seems to be no need for the joins in the select statement, and certainly no need for them to be left outer joins.

SELECT  Title
FROM    Film
WHERE   FilmID IN (SELECT FilmID FROM FilmGenre WHERE Genre IN ('Comedy', 'Horror'))

or in some RDBMS joins are more efficient than subqueries in where clauses (Read more...)

SELECT  Film.Title
FROM    Film
        INNER JOIN
        (   SELECT  FilmID 
            FROM    FilmGenre 
            WHERE   Genre IN ('Comedy', 'Horror')
            GROUP BY FilmID
        ) g
            ON g.FileID = Film.FilmID

EDIT:

If what is required are films that are ONLY Comedy and Horror Then you'll need something like:

SELECT  Film.Title
FROM    Film
        INNER JOIN
        (   SELECT  FilmID 
            FROM    FilmGenre 
            GROUP BY FilmID
            HAVING COUNT(DISTINCT CASE WHEN Genre IN ('Comedy', 'Horror') THEN Genre END) = 2 
            AND    COUNT(DISTINCT Genre) = 2
        ) g
            ON g.FileID = Film.FilmID

The same logic still applies of moving the subquery to an IN clause depending on RDBMS for optimisation.

Will return movies that belong to other genres, as well as 'Comedy' and 'Horror'. — , Feb 15 '12 at 13:33

score 0 · Answer 3 · 2012-02-15T13:47:45.363

0

Try:

select title from film f
where not exists
(select null
 from filmgenre g
 where f.filmid = g.filmid and
       g.genre NOT IN ('Comedy', 'Horror')
)

(Assuming there will always be at least one genre assigned to a film.)

EDIT: Alternatively, if there can be films not assigned to any genres, try:

select max(f.title) title 
from film f
join filmgenre g on f.filmid = g.filmid
group by f.filmid
having count(distinct g.genre) = 
       count(distinct case when g.genre in ('Comedy', 'Horror') then g.genre end)

edited Feb 15 '12 at 13:47

answered Feb 15 '12 at 13:31

1

This will not ensure that selected films do have 'Comedy' and 'Horror' genres – Sergey Kudriavtsev Feb 15 '12 at 13:32
It will, if there is always at least one genre assigned to a film. – Feb 15 '12 at 13:35
The edit still does not work as required. If a film only has an entry for "Comedy" in filmGenre each side of the `HAVING` Clause will be 1, therefore it will evaulate to true and return films that do not satisfy the criteria of being comedy AND horror. – GarethD Feb 15 '12 at 14:15
@GarethD: I understood the requirement as being **any** of "Comedy" or "Horror" (but nothing else), not **both** "Comedy" and "Horror". – Feb 15 '12 at 14:38

sql finding movie(s) with specific genres

3 Answers3