Блог

Я пытаюсь выяснить, как я могу получить Daniel Radcliffe историю карьеры. Однако я получаю результаты, которые не имеют отношения к Daniel Radcliffe .

 CREATE TABLE person (
  id integer primary key,
  name text not null
);

CREATE TABLE movie (
  id integer primary key,
  name text not null
);

CREATE TABLE casts (
  movie_id integer not null,
  person_id integer not null,
  played_as text not null,
  foreign key (movie_id) references movie(id),
  foreign key (person_id) references person(id)
);

CREATE TABLE crew (
  movie_id integer not null,
  person_id integer not null,
  job text not null,
  foreign key (movie_id) references movie(id),
  foreign key (person_id) references person(id)
);

CREATE TABLE famous_level (
  movie_id integer not null,
  person_id integer not null,
  level integer not null,
  foreign key (movie_id) references movie(id),
  foreign key (person_id) references person(id)
);

INSERT INTO person (id, name) VALUES (1, 'Daniel Radcliffe'), (2, 'Emma Watson'), (3, 'Robert Downey Jr.'), (4, 'Joss Whedon');
INSERT INTO movie (id, name) VALUES (1, 'Harry Potter movie'), (2, 'The Avengers');
INSERT INTO casts (movie_id, person_id, played_as) VALUES (1, 1, 'Harry Potter'), (1, 2, 'Hermione Granger'), (2, 3, 'Tony Stark');
INSERT INTO crew (movie_id, person_id, job) VALUES (1, 1, 'Writer'), (1, 4, 'Director'), (2, 2, 'Director'), (2, 1, 'Writer');
INSERT INTO famous_level (movie_id, person_id, level) VALUES (1, 1, 1), (1, 2, 3), (2, 3, 2), (1, 4, 2);
  

Я знаю, что Daniel Radcliffe идентификатор этого 1

 SELECT
  m.name,
  ct.played_as,
  cr.job
FROM movie m
JOIN crew cr ON m.id = cr.movie_id
JOIN casts ct ON m.id = ct.movie_id
JOIN famous_level f ON m.id = f.movie_id
JOIN person p ON cr.person_id = p.id
WHERE p.id = 1
ORDER BY f.level ASC
  

Я хочу получить результат как нечто подобное этому, где в столбце played_as или job есть значения NULL

  -------------------- -------------- -------- 
|        name        |  played_as   |  job   |
 -------------------- -------------- -------- 
| Harry Potter movie | Harry Potter | NULL   |
| Harry Potter movie | NULL         | Writer |
| The Avengers       | NULL         | Writer |
 -------------------- -------------- -------- 
  

Результат, который я получаю: http://www.sqlfiddle.com /#!17/28dd0

Поскольку вы хотите получить одну строку для каждого played_as или job для каждого фильма, вам необходимо создать таблицу со всеми значениями played_as и job , что вы можете сделать с UNION помощью запроса. Этот запрос возвращается NULL job , когда played_as он действителен, и наоборот. Затем его можно JOIN отредактировать в movie , person и famous_level таблицы для фильтрации по person_id и упорядочить по level :

 SELECT m.name,
       r.played_as,
       r.job
FROM movie m
JOIN (
  SELECT movie_id, person_id, played_as, NULL as job
  FROM casts
  UNION ALL 
  SELECT movie_id, person_id, NULL, job
  FROM crew
) r ON r.movie_id = m.id
JOIN person p ON p.id = r.person_id
LEFT JOIN famous_level f ON f.movie_id = m.id AND f.person_id = p.id
WHERE p.id = 1
ORDER BY COALESCE(f.level, 99)
  

Вывод:

 name                played_as       job
Harry Potter movie  (null)          Director
Harry Potter movie  (null)          Writer
Harry Potter movie  Harry Potter    (null)
The Avengers        (null)          Writer
  

Демонстрация на SQLFiddle

Обратите внимание, что вам нужно JOIN famous_level использовать оба movie_id и person_id избегать получения дополнительных строк в выходных данных. Поскольку не все комбинации фильмов / персонажей имеют a famous_level , вам нужно использовать a LEFT JOIN и COALESCE для level значения, когда оно есть NULL .