SQL Query, list from BOTH values?-CodePudding

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;

CodePudding user response：

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.

CodePudding user response：

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.