Search code examples
mysqlsqlselectinner-join

MySQL Exclusively get action movies

I have two tables, director and movie, they're linked by dirnr.

I want to select all Directors that have exclusively made action movies.

This is what I have right now:

SELECT distinct `name`
FROM`director`
INNER JOIN `movie` ON movie.dirnr = director.dirnr
WHERE movie.genre = 'action'

EDIT: This returns all directors that have ever made an action movie, I want it to return directors that have Exclusively made action movies and no other genres.

Solution

  • Your query just lists the directors that made at least one action movie. To test for exclusivity you can do this:

    SELECT distinct `name`
FROM`director`
INNER JOIN `movie` ON movie.dirnr = director.dirnr
WHERE movie.genre = 'action' 
AND NOT EXISTS (
    SELECT 1 
    FROM movie m 
    WHERE m.dirnr = director.dirnr 
    AND m.genre != 'action'
)