Cant understand why my approach isnt right when iam adding a condition with "and" to look-CodePudding

so i get some data from IMDB for Movies and i wanted to find the movies that both Jonny Depp and Helena Bonham Carter starred in them i tried this code

SELECT title
FROM movies
JOIN stars ON stars.movie_id = movies.id
JOIN people ON people.id = stars.person_id
**WHERE name = "Johnny Depp" AND name  = "Helena Bonham Carter")**

the last part seems to not work this way and i have to write it like this

SELECT title
FROM movies
JOIN stars ON stars.movie_id = movies.id
JOIN people ON people.id = stars.person_id
WHERE name = "Johnny Depp" AND movie_id IN 
(SELECT movie_id FROM people JOIN stars on stars.person_id = people.id WHERE name  = "Helena Bonham Carter")

Why using the first aproach i dont get the results?

CodePudding user response：

The column name cannot be two different values at the same time, which is what your predicate is testing.

Instead something like the following (untested) should work, we filter on the name being in the set of two names and count to make sure only movies with two unique names are returned:

select m.title
from movies m
join stars s on s.movie_id = m.id
join people p on p.id = s.person_id
where p.name in('Johnny Depp', 'Helena Bonham Carter')
group by m.title
having Count(distinct p.name) = 2;

CodePudding user response：

It cannot be Johnny and Helena at the same time. You could use your IN approach or EXISTS:

SELECT title
FROM movies m
WHERE EXISTS
(
    SELECT 1 FROM stars s
    WHERE s.movie_id = m.movie_id 
    AND s.name = "Johnny Depp"
)
AND EXISTS
(
    SELECT 1 FROM stars s
    WHERE s.movie_id = m.movie_id 
    AND s.name  = "Helena Bonham Carter"
)