Check if the sub-query includes all of the stated values-CodePudding

I'm trying to find movie_ids for movies that both johnny Depp and Helena Bonham Carted played in. I'm using SQLite3 as software and the famous IMDB dataset.

The result I got: ids for movies that either of them played

The Desired result: ids for movies that both of them played in

 select movie_id from stars where person_id in
 (select id from people where name in 
 ("Johnny Depp", "Helena Bonham Carter"))

);

CodePudding user response：

SELECT s.movie_id
FROM stars s
INNER JOIN people p ON p.id = s.person_id
WHERE p.name in ('Johnny Depp', 'Helena Bonham Carter')
GROUP BY s.movie_id
HAVING COUNT(DISTINCT p.id) = 2