help with a sql query joining 2 many-many tables

Question

i want help how to solve this sql problem. suppose i have 3 tables

Movie

• ID
• Name

Genre

• ID
• Name

Movie_Genre (this one is the link for many to many)

• FK_MovieID
• FK_GenreID

i want to select all the movies that are of genre 1 and genre 3
how is this possible?
i can only select the movies of 1 genre but not the movies that are of 2 genres using

    SELECT Movie.ID, Movie.Name 
      FROM Movies
INNER JOIN Movie_Genre ON Movie_Genre.FK_MovieID=Movie.ID
                      AND Movie_Genre.FK_GenreID = 1

Answer 1

Use:

  SELECT m.id, 
         m.name
    FROM MOVIE m
    JOIN MOVIE_GENRE mg ON mg.fk_movieid = m.id
                       AND mg.fk_genreid IN (1, 3)
GROUP BY m.id, m.name
  HAVING COUNT(DISTINCT mg.fk_genreid) = 2

The last line is key to getting rows from both genre's - the DISTINCT means duplicate associations (IE: two instances of genre 1) will be ignored because they are false positives. But the count must equal the number of genres you are looking for.

But COUNT(DISINCT isn't supported by all databases. You should mention what you are using - if not by tag, then in the question... If the primary key for the MOVIE_GENRE table is both fk_movieid and fk_genreid, then it's not an issue. Next best thing would be that both the columns are in a unique constraint/index...