CODEWITHSUNDEEP
sqlh2outer-join
Bassinator
Bassinator

Reputation: 1724

SQL select the actor and movie name from every actor that has worked with a particular actor

This is a HW assignment, so please no exact answers if you can help it; I want to learn, not have it done for me.

(The create table statments are at the end of this post)

My task is to find all of the actors who have been in a movie with Tom Hanks, ordered by movie title, using 2 queries.

So far I have been able to create the following query; I know that my join is wrong, but I'm not sure why. How can I think about this differently? I feel like I'm close to the answer, but not quite there.

SELECT actor.name, movie.title FROM actor
LEFT OUTER JOIN character.movie_id ON movie.id IN
(
    -- Get the ID of every movie Tom Hanks was in
    SELECT movie_id FROM actor
    INNER JOIN character ON character.actor_id = actor.id
    WHERE actor.name = 'Tom Hanks'
)
WHERE actor.name != 'Tom Hanks'
ORDER BY movie.title;

Here are the create table statments for the schema:

create table actor (
    id varchar(100),name varchar(100),
    constraint pk_actor_id primary key (id));

create table movie( 
    id varchar(100),
    title varchar(100),
    year smallint unsigned,
    mpaa_rating varchar(10),
    audience_score smallint unsigned,
    critics_score smallint unsigned,
    constraint pk_id primary key(id));

create table character(
    actor_id varchar(100),
    movie_id varchar(100),
    character varchar(100),
    constraint pk_character_id primary key(movie_id, actor_id, character),
    constraint fk_actor_id foreign key (actor_id) references actor (id),
    constraint fk_movie_id foreign key (movie_id) references movie (id));

Upvotes: 1

Views: 2487

Answers (2)

Kevin
Kevin

Reputation: 7309

A left outer join will give you every entry in the left table (actor). If it has a corresponding value in the right table, it will give you that value, otherwise, you will get a null value returned.

Additionally, you join a table. In your query, you are trying to join a column

Upvotes: 3

Littlefoot
Littlefoot

Reputation: 142705

Something like this, perhaps? FWTH is films with Tom Hanks.

select mo.title, ac.name 
from character ch join 
  (select m.movie_id
     from character c join movie m on c.movie_id = m.id
       join actor a on a.id = c.actor_id
     where a.name = 'Tom Hanks'
  ) fwth on ch.movie_id = fwth.movie_id
  join actor ac on ac.id = ch.actor_id
  join movie mo on mo.id = fwth.movie_id
order by mo.title;

Upvotes: 1

Related Questions