How to select data based on foreign keys in Postgres?

Question

I have a movies Postgres database, I want to efficiently list all movies played by an actor, And list all actors in a movie.

Currently I'm using these two SELECT statements (Which I bileive there is better way to do that):

SELECT title FROM movies,castings WHERE movies.id = castings.movieid and castings.actorid  = (SELECT id FROM actors where name = $name and sname = $sname);

SELECT name, sname FROM actors,castings WHERE actors.id = castings.actorid and castings.movieid  = (SELECT id FROM movies where title = $title);

The Database:

CREATE SEQUENCE directors_id_seq;
CREATE TABLE directors  (
    id BIGINT PRIMARY KEY DEFAULT nextval('directors_id_seq'),
    name TEXT,
    sname TEXT
);

CREATE SEQUENCE actors_id_seq;
CREATE TABLE actors (
    id BIGINT PRIMARY KEY DEFAULT nextval('actors_id_seq'),
    name TEXT,
    sname TEXT
);

CREATE SEQUENCE movies_id_seq;
CREATE TABLE movies (
    id BIGINT PRIMARY KEY DEFAULT nextval('movies_id_seq'),
    title TEXT NOT NULL,
    year INT NOT NULL,
    votes INT,
    score INT,
    director BIGINT NOT NULL REFERENCES directors (id)
);

CREATE TABLE castings   (
    movieid BIGINT REFERENCES movies (id),
    actorid BIGINT REFERENCES actors (id),
    ord INT,
    PRIMARY KEY (movieid, actorid)
);

How can I list all movies played by an actor? How can I list all actors played on a movie?

-- Thanks,

Matt · Accepted Answer · 2017-08-04 00:11:00Z

11

This should do it:

All movies played by an actor

SELECT M.*
FROM movies M
INNER JOIN castings C ON C.movieid = M.id
INNER JOIN actors A ON A.id = C.actorid
WHERE A.name = $name
AND A.sname = $sname

All actors played on a movie

SELECT A.*
FROM actors A
INNER JOIN castings C ON C.actorid = A.id
INNER JOIN movies M ON M.id = C.movieid
WHERE M.title = $title

answered Aug 4, 2017 at 0:11

Matt

7,01512 gold badges71 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to select data based on foreign keys in Postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related