如何在Postgres中基于外键选择数据？

Question

我有一个电影的Postgres数据库，我想高效地列出所有由演员扮演的电影，并列出电影中的所有演员。

目前我正在使用这两个SELECT语句（我认为有更好的方法来做到这一点）:

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

数据库：

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

我该如何列出一个演员所演过的所有电影？ 我该如何列出一部电影中所有的演员？

-- 谢谢，

- hythm

Answer 1

这应该可以解决问题：

演员出演的所有电影

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

电影中的所有演员

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

- Matt