SQL Query, list from BOTH values?

Question

Pretty sure this can be answered quite quickly but I just can't seem to find a solution online. Might I add also (if you haven't already figured out) that I'm a complete beginner.

The following query lists all movies starring Johnny Depp and all movies starring Helena Bonham Carter. How do I list all movies starring BOTH Johnny and Helena?

Thank you!

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

Answer 1

See if this works for you, aggregate the title and filter where there are only 2 - assuming a person can only star in a movie once.

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(*) = 2
order by m.title;

Also note that using aliases improves the readability and string literals should be in 'single' quotes, double quotes are reserved for delimiters in most databases.

Answer 2

Here is an alternative. Use IN or EXISTS:

select title
from movies
where id in (select movie_id from stars where person_id = 
              (select id from people where name = 'Johnny Depp')
            )
  and id in (select movie_id from stars where person_id = 
              (select id from people where name = 'Helena Bonham Carter')
            )
order by title;

This is very quick for the DBMS to do - even with thousands of actors in your database. I suppose the DBMS would get each actor with an index, get their movies with another index and then get the movie title(s) with yet another index. This is extemely fast.