SQL Query returns no results while using AND keyword-CodePudding

I am currently beginning with SQL using SQLite3 in CS50's Codespace. There is a simple problem regarding a query which I need to run.

I need to find out movies where BOTH Johnny Depp and Helena Bonham Carter were starring. MY query is as follows

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

Now this returns 0 results. The query works when I only use one of the two names and remove the AND clause.

But as per my understanding the AND statement should work, is that the case or there is a lapse in my understanding?

CodePudding user response：

you could use exists and look for the existence of both actors.

SELECT title from movies
JOIN stars a ON a.movie_id = movies.id
WHERE exists (select 1 from people b
              where b.people.id = a.person_id
              and b.name = 'Johnny Depp')
AND exists (select 1 from people c
              where c.people.id = a.person_id
              and c.name = 'Helena Bonham Carter')

CodePudding user response：

you could also count up the actors that are Johnny Depp or Helena Bonham Carter and look for rows where the count is 2

Select title,
sum(case when p.name in ('Johhny Depp', 'Helena Bonham Carter') then 1 else 0 end) as cnt
from movies m
join stars s on s.movie_id = m.id
join people p on p.id = a.person_id
group by title
having cnt > 1