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.