CODEWITHSUNDEEP
mysql
Azevedo
Azevedo

Reputation: 2189

MySQL: WHERE ... IN()

SELECT * from movies
 INNER JOIN movie_genres ON movies.id_movie=movie_genres.fk_id_movie
 INNER JOIN genres ON movie_genres.fk_id_genre=genres.id_genre
WHERE genre_name IN ('action','crime');

movie_genres has just 2 columns (FK): - fk_id_movie - fk_id_genre

[movies]--------[movies_genres]-------[genres]

This returns an OR result: any movie which genre is 'action' OR 'crime';

Is it possible to write this with an AND result. Return only movies that have the 2 genres altogether?

Upvotes: 1

Views: 111

Answers (6)

Rahul
Rahul

Reputation: 77876

SELECT * from movies
where id in 
(
select movie_id from 
movie_genres group by genre having count(genre) = 3
)

Upvotes: 0

Pete
Pete

Reputation: 4622

You should only select the columns you need, not all of them (*) unless you really need every single column of both tables in your results. It's better for performance.

SELECT movies.col1, movies.col2 
FROM `movies` 
    INNER JOIN `movie_genres` ON movies.id = movie_genres.id_movie 
GROUP BY movies.id
HAVING SUM( DISTINCT movie_genres.genre ) = 6

Upvotes: 0

Mihai
Mihai

Reputation: 26784

SELECT * from movies INNER JOIN movie_genres
ON movies.id=id_movie  WHERE genre IN ('1','2','3')
GROUP BY movies.id
HAVING COUNT(DISTINCT genre)= 3;

Upvotes: 1

Mureinik
Mureinik

Reputation: 311308

What your query does is to join a single line of movies with a single row of movie_genres and then evaluate it - which, as you suggested, is not the result you're expecting. Instead, you need to build some query which joins movies with some aggregation of movie_genres.

Gordon's answer is a great example of this. A different approach, assuming the combination of id_movie and genre is unique, is to count how many of the genres your movie matches:

SELECT *
FROM movies 
INNER JOIN (SELECT    id_movie, COUNT(*) AS matches
            FROM      movie_genres
            WHERE     genre IN ('1', '2', '3')
            GROUP  BY id_movie) genres ON movies.id=id_movie
WHERE      matches = 3

Upvotes: 1

anyaelise
anyaelise

Reputation: 92

Yes, just use the AND keyword...

SELECT * from movies INNER JOIN movie_genres ON movies.id=id_movie WHERE (genre = 1 AND genre = 2 AND genre = 3);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Yes. A good way to do this is with aggregation and a having clause:

SELECT *
from movies INNER JOIN
     movie_genres
     ON movies.id=id_movie
GROUP BY movies.id
HAVING sum(genre = '1') > 0 and
       sum(genre = '2') > 0 and
       sum(genre = '3') > 0;

Upvotes: 2

Related Questions