CODEWITHSUNDEEP
postgresqljoinnullleft-join
SQL Learner 1
SQL Learner 1

Reputation: 147

Postgresql How do I join these tables to get the correct output?

I am trying to find out which people are noted as being principal actors in a movie, but aren't noted as playing a character in that movie.

The schema I have is:

CREATE TABLE public.movies (
    id integer NOT NULL,
    title text NOT NULL,
    year_made public.yeartype NOT NULL,
    runtime public.minutes,
    rating double precision,
    nvotes public.counter
);

CREATE TABLE public.people (
    id integer NOT NULL,
    name text NOT NULL,
    year_born public.yeartype,
    year_died public.yeartype
);

CREATE TABLE public.plays (
    movie_id integer NOT NULL,
    person_id integer NOT NULL,
    "character" text NOT NULL
);

CREATE TABLE public.principals (
    movie_id integer NOT NULL,
    ordering public.counter NOT NULL,
    person_id integer NOT NULL,
    role text NOT NULL

So far the query I have used works for some actors, however I think I have done the joins incorrectly as there is another actor who is a principal actor but is given a character which it shouldn't have (character name is from another movie they were in). This is my query:

select name as actor, movies.title as movie,character
from principals
inner join people on principals.person_id=people.id
inner join movies on principals.movie_id=movies.id
left outer join plays on principals.person_id=plays.person_id
where principals.role = 'actor' and character is null

Can anyone help me with this?

This is a summary of results and the join adds all the persons character names to every movie they were principal in. Results

https://drive.google.com/file/d/1NVRLiYBVbKuiazynx9Egav7c4_VHFEzP/view?usp=sharing

Upvotes: 0

Views: 87

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35603

I think a second join condition is required for table plays as shown in bold below:

FROM principals
    LEFT JOIN plays ON principals.person_id = plays.person_id
        AND principles.movie_id = plays.movie_id
WHERE principals.role = 'actor'
    AND plays.person_id IS NULL

However I feel that a not exists approach will make this query easier to understand:

SELECT
      people.name AS actor
    , movies.title AS movie
    , plays.character
FROM principals
INNER JOIN people ON principals.person_id = people.id
INNER JOIN movies ON principals.movie_id = movies.id
WHERE principals.ROLE = 'actor'
    AND NOT EXISTS (
        SELECT NULL
        FROM plays
        WHERE principals.person_id = plays.person_id
            AND principals.movie_id = plays.movie_id
        )

Upvotes: 1

Bohemian
Bohemian

Reputation: 425278

You’re pretty close, but need to also join plays using movie.id:

select name as actor, movies.title as movie
from principals
join people on principals.person_id=people.id
join movies on principals.movie_id=movies.id
left join plays on principals.person_id=plays.person_id
  and movies.id=principals.movie_id
where principals.role = 'actor'
and character is null

Removed character from select because it’s always null.

Upvotes: 0

Related Questions