SQL Journey: Blog#7

Challenge: Sequels in SQL

Given data:

CREATE TABLE movies (id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    released INTEGER,
    sequel_id INTEGER);
INSERT INTO movies 
    VALUES (1, "Harry Potter and the Philosopher's Stone", 2001, 2);
INSERT INTO movies 
    VALUES (2, "Harry Potter and the Chamber of Secrets", 2002, 3);
INSERT INTO movies 
    VALUES (3, "Harry Potter and the Prisoner of Azkaban", 2004, 4);
INSERT INTO movies 
    VALUES (4, "Harry Potter and the Goblet of Fire", 2005, 5);
INSERT INTO movies 
    VALUES (5, "Harry Potter and the Order of the Phoenix ", 2007, 6);
INSERT INTO movies 
    VALUES (6, "Harry Potter and the Half-Blood Prince", 2009, 7);
INSERT INTO movies 
    VALUES (7, "Harry Potter and the Deathly Hallows – Part 1", 2010, 8);
INSERT INTO movies 
    VALUES (8, "Harry Potter and the Deathly Hallows – Part 2", 2011, NULL);

Step 1

We've created a table with all the 'Harry Potter' movies, with a sequel_id column that matches the id of the sequel for each movie. Issue a SELECT that will show the title of each movie next to its sequel's title (or NULL if it doesn't have a sequel).

As I was writing the code for this one, I encountered an error which signifies that I do not understand the concept fully.

Here is my code:

SELECT movies.title, sequel.title AS sequel_title 

    FROM movies 

    LEFT OUTER JOIN movies sequel 

    ON sequel.sequel_id = movies.id;

Query results:

title	sequel_title
Harry Potter and the Philosopher's Stone	NULL
Harry Potter and the Chamber of Secrets	Harry Potter and the Philosopher's Stone
Harry Potter and the Prisoner of Azkaban	Harry Potter and the Chamber of Secrets
Harry Potter and the Goblet of Fire	Harry Potter and the Prisoner of Azkaban
Harry Potter and the Order of the Phoenix	Harry Potter and the Goblet of Fire
Harry Potter and the Half-Blood Prince	Harry Potter and the Order of the Phoenix
Harry Potter and the Deathly Hallows – Part 1	Harry Potter and the Half-Blood Prince
Harry Potter and the Deathly Hallows – Part 2	Harry Potter and the Deathly Hallows – Part 1

The problem is I have read some of the Harry Potter books and also watched the films so I know all of the sequels and the movies that precede it. The above table is wrong. So there is a need for me to correct it.

Looking at my code, I specified to show the title of each movie in the movies table in the first column. Next, I specified the sequel_title in the second column. My mistake lies in the "ON sequel.sequel_id = movies.id" though I cannot exactly remedy the bug. So what I did, is to take this step-by-step.

First, I tried to find a way so that I can show the movie title and the sequel_id in one table.

Code:

SELECT movies.title, sequel_id

    FROM movies;

Query results:

title	sequel_id
Harry Potter and the Philosopher's Stone	2
Harry Potter and the Chamber of Secrets	3
Harry Potter and the Prisoner of Azkaban	4
Harry Potter and the Goblet of Fire	5
Harry Potter and the Order of the Phoenix	6
Harry Potter and the Half-Blood Prince	7
Harry Potter and the Deathly Hallows – Part 1	8
Harry Potter and the Deathly Hallows – Part 2	NULL

What's left for me to do, is to convert each of the sequel_id in the second column to its corresponding movie title. This is where I will use "LEFT OUTER JOIN" since I want to ensure that all Harry Potter movie titles are included in the first column while also displaying its corresponding sequel titles.

Code:

SELECT movies.title, sequel_movies.title

    FROM movies

    LEFT OUTER JOIN movies sequel_movies;

Query results:

title	title
Harry Potter and the Philosopher's Stone	Harry Potter and the Philosopher's Stone
Harry Potter and the Philosopher's Stone	Harry Potter and the Chamber of Secrets
Harry Potter and the Philosopher's Stone	Harry Potter and the Prisoner of Azkaban
Harry Potter and the Philosopher's Stone	Harry Potter and the Goblet of Fire
Harry Potter and the Philosopher's Stone	Harry Potter and the Order of the Phoenix
Harry Potter and the Philosopher's Stone	Harry Potter and the Half-Blood Prince
Harry Potter and the Philosopher's Stone	Harry Potter and the Deathly Hallows – Part 1
Harry Potter and the Philosopher's Stone	Harry Potter and the Deathly Hallows – Part 2
Harry Potter and the Chamber of Secrets	Harry Potter and the Philosopher's Stone
Harry Potter and the Chamber of Secrets	Harry Potter and the Chamber of Secrets
Harry Potter and the Chamber of Secrets	Harry Potter and the Prisoner of Azkaban
Harry Potter and the Chamber of Secrets	Harry Potter and the Goblet of Fire
Harry Potter and the Chamber of Secrets	Harry Potter and the Order of the Phoenix
Harry Potter and the Chamber of Secrets	Harry Potter and the Half-Blood Prince
Harry Potter and the Chamber of Secrets	Harry Potter and the Deathly Hallows – Part 1
Harry Potter and the Chamber of Secrets	Harry Potter and the Deathly Hallows – Part 2
Harry Potter and the Prisoner of Azkaban	Harry Potter and the Philosopher's Stone
Harry Potter and the Prisoner of Azkaban	Harry Potter and the Chamber of Secrets
Harry Potter and the Prisoner of Azkaban	Harry Potter and the Prisoner of Azkaban
Harry Potter and the Prisoner of Azkaban	Harry Potter and the Goblet of Fire
Harry Potter and the Prisoner of Azkaban	Harry Potter and the Order of the Phoenix
Harry Potter and the Prisoner of Azkaban	Harry Potter and the Half-Blood Prince
Harry Potter and the Prisoner of Azkaban	Harry Potter and the Deathly Hallows – Part 1
Harry Potter and the Prisoner of Azkaban	Harry Potter and the Deathly Hallows – Part 2
Harry Potter and the Goblet of Fire	Harry Potter and the Philosopher's Stone
Harry Potter and the Goblet of Fire	Harry Potter and the Chamber of Secrets
Harry Potter and the Goblet of Fire	Harry Potter and the Prisoner of Azkaban
Harry Potter and the Goblet of Fire	Harry Potter and the Goblet of Fire
Harry Potter and the Goblet of Fire	Harry Potter and the Order of the Phoenix
Harry Potter and the Goblet of Fire	Harry Potter and the Half-Blood Prince
Harry Potter and the Goblet of Fire	Harry Potter and the Deathly Hallows – Part 1
Harry Potter and the Goblet of Fire	Harry Potter and the Deathly Hallows – Part 2
Harry Potter and the Order of the Phoenix	Harry Potter and the Philosopher's Stone
Harry Potter and the Order of the Phoenix	Harry Potter and the Chamber of Secrets
Harry Potter and the Order of the Phoenix	Harry Potter and the Prisoner of Azkaban
Harry Potter and the Order of the Phoenix	Harry Potter and the Goblet of Fire
Harry Potter and the Order of the Phoenix	Harry Potter and the Order of the Phoenix
Harry Potter and the Order of the Phoenix	Harry Potter and the Half-Blood Prince
Harry Potter and the Order of the Phoenix	Harry Potter and the Deathly Hallows – Part 1
Harry Potter and the Order of the Phoenix	Harry Potter and the Deathly Hallows – Part 2
Harry Potter and the Half-Blood Prince	Harry Potter and the Philosopher's Stone
Harry Potter and the Half-Blood Prince	Harry Potter and the Chamber of Secrets
Harry Potter and the Half-Blood Prince	Harry Potter and the Prisoner of Azkaban
Harry Potter and the Half-Blood Prince	Harry Potter and the Goblet of Fire
Harry Potter and the Half-Blood Prince	Harry Potter and the Order of the Phoenix
Harry Potter and the Half-Blood Prince	Harry Potter and the Half-Blood Prince
Harry Potter and the Half-Blood Prince	Harry Potter and the Deathly Hallows – Part 1
Harry Potter and the Half-Blood Prince	Harry Potter and the Deathly Hallows – Part 2
Harry Potter and the Deathly Hallows – Part 1	Harry Potter and the Philosopher's Stone
Harry Potter and the Deathly Hallows – Part 1	Harry Potter and the Chamber of Secrets
Harry Potter and the Deathly Hallows – Part 1	Harry Potter and the Prisoner of Azkaban
Harry Potter and the Deathly Hallows – Part 1	Harry Potter and the Goblet of Fire
Harry Potter and the Deathly Hallows – Part 1	Harry Potter and the Order of the Phoenix
Harry Potter and the Deathly Hallows – Part 1	Harry Potter and the Half-Blood Prince
Harry Potter and the Deathly Hallows – Part 1	Harry Potter and the Deathly Hallows – Part 1
Harry Potter and the Deathly Hallows – Part 1	Harry Potter and the Deathly Hallows – Part 2
Harry Potter and the Deathly Hallows – Part 2	Harry Potter and the Philosopher's Stone
Harry Potter and the Deathly Hallows – Part 2	Harry Potter and the Chamber of Secrets
Harry Potter and the Deathly Hallows – Part 2	Harry Potter and the Prisoner of Azkaban
Harry Potter and the Deathly Hallows – Part 2	Harry Potter and the Goblet of Fire
Harry Potter and the Deathly Hallows – Part 2	Harry Potter and the Order of the Phoenix
Harry Potter and the Deathly Hallows – Part 2	Harry Potter and the Half-Blood Prince
Harry Potter and the Deathly Hallows – Part 2	Harry Potter and the Deathly Hallows – Part 1
Harry Potter and the Deathly Hallows – Part 2	Harry Potter and the Deathly Hallows – Part 2

The query results are so long since it mapped out every single Harry Potter movie title to each of the 8 Harry Potter movies. This is not our intention. We still lack a proper "ON" clause. And here is the following:

Code:

SELECT movies.title, sequel_movies.title AS sequel_title

    FROM movies

    LEFT OUTER JOIN movies sequel_movies

    ON movies.sequel_id = sequel_movies.id;

We select two titles - a movie title and a sequel title. The movie title, located in the first column of our output table, is taken from the first table called movies while the sequel title will be taken from a second table called sequel_movies. Since this is a SELF-JOIN exercise, we expect the contents of the first table to be the same as the second table. Only their names are different to properly execute a self-join. In other words, we need to rename the second table for us to be able to convert the sequel_id from the first table to its corresponding movie title in the second table. Therefore, only when the sequel_id in the first table is equal to the movie id in the second table, we can effectively extract the sequel movie title from the second table.

Query Results:

title	sequel_title
Harry Potter and the Philosopher's Stone	Harry Potter and the Chamber of Secrets
Harry Potter and the Chamber of Secrets	Harry Potter and the Prisoner of Azkaban
Harry Potter and the Prisoner of Azkaban	Harry Potter and the Goblet of Fire
Harry Potter and the Goblet of Fire	Harry Potter and the Order of the Phoenix
Harry Potter and the Order of the Phoenix	Harry Potter and the Half-Blood Prince
Harry Potter and the Half-Blood Prince	Harry Potter and the Deathly Hallows – Part 1
Harry Potter and the Deathly Hallows – Part 1	Harry Potter and the Deathly Hallows – Part 2
Harry Potter and the Deathly Hallows – Part 2	NULL