CODEWITHSUNDEEP
sqlsqlite
Mohamed Ehab
Mohamed Ehab

Reputation: 31

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

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'))

Upvotes: 1

Views: 194

Answers (2)

forpas
forpas

Reputation: 164089

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

Upvotes: 1

JoshuaG
JoshuaG

Reputation: 400

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')

Upvotes: 0

Related Questions