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;
Advertisement
Answer
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.