LEFT JOIN with WHERE and AND parameters

Ethrak Published at Dev

Ethrak

I have the following tables :

Movies :

+----------+--------------+
| movie_id |     title    |
+----------+--------------+
|    1     | Wonder Woman |
+----------+--------------+

Cast :

+----------+--------------+
| id       |     name     |
+----------+--------------+
|    1     |   Gal Gadot  |
+----------+--------------+
|    2     |  Chris Pine  |
+----------+--------------+

Movie_cast :

+----------+--------------+--------------+
| id       |   movie_id   |    cast_id   |
+----------+--------------+--------------+
|    1     |       1      |       1      |
+----------+--------------+--------------+
|    2     |       1      |       2      |
+----------+--------------+--------------+

Basicly, Gal Gadot and Chris Pine are both part of the movie Wonder Woman.

My problem is that I want to find all of the movies in which they are both in. I don't want any movie in which they play separately, only where they are both are in the cast.

I tried something like this:

SELECT * FROM movies
LEFT JOIN movie_cast ON movie_cast.movie_id = movies.movie_id 
LEFT JOIN cast ON movie_cast.cast_id = cast.id
WHERE cast.id = 1 AND cast.id = 2

But the result was empty.

Pham X. Bach

You should use this:

SELECT m.movie_id, m.title 
FROM movies m
INNER JOIN movie_cast mc ON mc.movie_id = m.movie_id 
WHERE mc.cast_id = 1 OR mc.cast_id = 2
GROUP BY m.movie_id, m.title
HAVING COUNT(*) = 2; -- HAVING COUNT(DISTINCT mc.cast_id) = 2; -- for unnormalized data

No need LEFT JOIN, and you only need to JOIN casttable when you need to find by cast's name

Collected from the Internet

Please contact [email protected] to delete if infringement.