CODEWITHSUNDEEP
mysqlsqlsqlite
Chase Roberts
Chase Roberts

Reputation: 9376

SQL Query for multiple relationships to exist in a many to many JOIN

I have a many to many relationship between Movies and Genres. What I want to do is query for Action Comedy Movies. This is as close as I have gotten:

SELECT * FROM movies 
JOIN movies_genre ON (movies.id = movies_genre.movie_id) 
JOIN genres ON (movies_genre.genre_id = genres.id) 
WHERE (
  genres.genre = "Comedy" OR
  genres.genre = "Action & Adventure"
)

But this gives me all the movies that are Comedy or Adventure. If I change the OR to an AND then I get back an empty table. Is there a simple way to do this with one query?

Upvotes: 0

Views: 1610

Answers (4)

Chase Roberts
Chase Roberts

Reputation: 9376

I found something that works, but in no way do I believe it is the best way:

SELECT * FROM movies 
JOIN movies_genre ON (movies.id = movies_genre.movie_id) 
JOIN genres ON (movies_genre.genre_id = genres.id) 
WHERE (
  genres.genre = "Action"
  AND (
  movies.poster IN (
    SELECT movies.poster FROM movies 
    JOIN movies_genre ON (movies.id = movies_genre.id)
    JOIN genres ON (movies_genre.id = genres.id) 
    WHERE genres.genre = "Comedy"
    )
  )
)

Upvotes: 0

Stephan Lechner
Stephan Lechner

Reputation: 35154

Try...

select * from movies m
where m.id in (select movie_id from movies_genre join genres on (movies_genere.genre_id = genres.id) where genres.genre = 'Comedy')
and m.id in (select movie_id from movies_genre join genres on (movies_genere.genre_id = genres.id) where genres.genre = 'Action & Adventure')

Upvotes: 1

Anand
Anand

Reputation: 1123

Ugh, many-to-many relationships are the worst, requiring queries like this:

select *
from movies as m
where exists (
    select 1
    from movies_genre as mg
    inner join genres as g
    on g.id = mg.genre_id
    where mg.movie_id = m.id
    and g.genre in ('Action & Adventure', 'Comedy')
    group by g.id
    having count(*) = 2
    )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You want information about a movie, so SELECT * is not appropriate. The following query returns movie ids that match both genres:

SELECT mg.movie_id
FROM movies_genre mg JOIN
     genres g
     ON mg.genre_id = g.id
WHERE g.genre IN ('Comedy', 'Action & Adventure')
GROUP BY mg.movie_id
HAVING COUNT(*) = 2;

Notes:

  • Table aliases make the query much easier to write and to read.
  • IN is more sensible than a bunch of OR expressions.
  • The HAVING clause counts the number of matching genres. It assumes that genres are not repeated.
  • If you want full movie information, you can join that in using additional logic.

Upvotes: 1

Related Questions