CODEWITHSUNDEEP
sqlsubqueryinner-join
GreenFerret95
GreenFerret95

Reputation: 117

Find all actors who played in the same movie as Tom Hanks

I have the start of an sql statement where I am supposed to find all actors who have played in the same movie as Tom Hanks. 

select  a.name, m.title
from actor a 
inner join character c
inner join movie m

on a.id = c.actor_id
on c.movie_id = m.id

where a.name = 'Tom Hanks'

However this code only gives me all the names of movies Tom Hanks has played in, but not each actor.

The tables look like this

Actor:
  ID
  name

  Primary Key: ID      

Character:
  Actor_ID
  Movie_ID
  Character

  Primary Key: (actor_id, movie_id, character)
  Foreign Key: actor_id references actor(id)
  Foreign Key: movie_id references movie(id)

Movie:
  ID
  Title
  Year
  Mpaa_rating
  Audience_score
  Critics_score

  Primary Key: ID

I've been messing around for a while with different sub queries and inner joins but cannot seem to figure out what I'm doing wrong.

Upvotes: 1

Views: 1138

Answers (2)

Alex
Alex

Reputation: 210

I made an error before, needed an "in" not an "=" You should try this one too:

Select a.name, m.title
From actor a, movie m, character c
Where a.id = c.actor_id
And m.id = c.movie_id
And m.id in ( select movie_id from character where actor_id = (select id from actor where name = 'the name'))

Upvotes: 0

user1327961
user1327961

Reputation: 452

Broken out into subqueries for simplicity

-- get all actors that are in movies that tom hanks is in
SELECT  * 
FROM    actor 
WHERE   
    actor_id IN 
    (   -- get all actor ids that are in movies that tom hanks is in
        SELECT  actor_id 
        FROM    character 
        WHERE   
            movie_id IN 
            (   -- get all movies that tom hanks is in
                SELECT  movie_id 
                FROM    character
                WHERE   
                    actor_id IN 
                    (   -- get actor ids that equal tom hanks
                        SELECT id 
                        FROM actor 
                        WHERE name = 'Tom Hanks'
                    )
            )
    )

Upvotes: 1

Related Questions