How to SELECT from column WHERE we have multiple conditions in from another TABLE?

I have 3 TABLES:

1. movies which has title and id columns
2. stars which has person_id and movie_id columns
3. people which has id and name columns

I want to write a SQL query to list the titles of all movies in which both Johnny Depp and Helena Bonham Carter starred.

When I write my query I do not get the title which both Johnny Depp and Helena Bonham Carter starred

SELECT title
FROM movies
WHERE id IN (   SELECT movie_id
                FROM people
                JOIN stars
                ON stars.person_id = people.id
                WHERE name = "Helena Bonham Carter" 
                OR name = "Johnny Depp"
            )
GROUP BY title;

Answer

You can try the below –

SELECT title
FROM movies
WHERE id IN (   SELECT movie_id
                FROM people
                JOIN stars
                ON stars.person_id = people.id
                WHERE name in('Helena Bonham Carter' ,'Johnny Depp')
                group by movie_id
                having count(distinct name)=2
            )
GROUP BY title