Getting wrong answer when trying to output a an actors roles

I am trying to figure out how I can get Daniel Radcliffe‘s career history. However, I am getting results which aren’t relevant to 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);

---

I know the that Daniel Radcliffe‘s ID is 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

I want to get the result as something similar to this, where there are NULL values on either the played_as or job column

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

Result I get: http://www.sqlfiddle.com/#!17/28dd0

Answer

Because you want to get one row for each played_as or job for each movie, you need to create a table with all the played_as and job values, which you can do with a UNION query. That query returns NULL for job when played_as is valid and vice versa. It can then be JOINed to the movie, person and famous_level tables to filter on the person_id and order by the 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)

Output:

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

Demo on SQLFiddle

Note that you need to JOIN to famous_level on both movie_id and person_id to avoid getting extra rows in the output. Since not all movie/person combinations have a famous_level, you need to use a LEFT JOIN and COALESCE on the level value for when it is NULL.