I've been stuck on this for quite some time and did a lot of research on the AND
operator in SQL, but no matter how many variations I try I cannot get my table to output just 6 rows, with each row containing a movie that both Johnny Depp and Helena Bonham Carter starred in. I believe my table instead outputs 92 rows for the movies that both of them have been in in general, rather than together. Here is my code:
SELECT title FROM movies
WHERE id IN
(SELECT movie_id FROM stars WHERE person_id IN
(SELECT id FROM people WHERE name IN ('Johnny Depp', 'Helena Bonham Carter')));
Here is the output message:
Execution finished without errors.
Result: 98 rows returned in 1432ms
At line 1:
SELECT title FROM movies
JOIN stars
ON movies.id = stars.movie_id
WHERE id IN
(SELECT id WHERE person_id IN
And here is the resulting table itself (96 rows total):
Any suggestions or ideas to output just the movies the two starred in (six rows total) would be greatly appreciated.