Asking a condition based on two cases in the same column. sqlite3

user_14954350 0
Viewed 103

I am trying to insert into a new table all the names of the movies where helena bonhab carter and johnny depp starred in. I am selecting information firstfrom a table called movies which has a title and an id. and then from a table called stars which has a movie_id and a person_id. and lastly I am selecting from a table called people which has a id and name columns. I think my problem is in the union line but i don't know why.

CREATE TABLE jh( title TEXT)
INSERT INTO jh(title)
SELECT movies.title
FROM movies
WHERE movies.id in ( SELECT stars.movie_id FROM stars WHERE stars.person_id in(SELECT 
people.id FROM people WHERE people.name = ' Johnny Depp' UNION SELECT people.id FROM people 
WHERE people.name = 'Helena Bonham Carter'))
sqlite sql
2 Answers

You can join the tables, filter for the actors you want, group by movie and set the condition the HAVING clause:

SELECT m.title
FROM movies m
INNER JOIN stars s ON s.movie_id = m.id
INNER JOIN people p ON p.id = s.person_id
WHERE p.name IN ('Johnny Depp', 'Helena Bonham Carter')
GROUP BY m.id, m.title
HAVING COUNT(*) = 2 -- the number of the actors
user_104988280

I really suggest you don't do query like that. for example, you could select all names in one single WHERE condition. And be ready to use JOIN if you have several tables need to be connected.

SELECT M.Title
FROM Movies M INNER JOIN Stars S on M.id = S.Movie_Id 
INNER JOIN People P on S.PersonID = P.PersonID
WHERE P.Name in ('Johnny Depp','Helena Bonham Carter')
user_106264660
Related