Think Summer: Notes on how to find Friends episodes — 2022

We first load the SQL database:

%sql sqlite:////anvil/projects/tdm/data/movies_and_tv/imdb.db

Then we load the first few lines of the titles table.

%%sql
SELECT * FROM titles LIMIT 5;

and we check which shows after 1992 were called Friends.

%%sql
SELECT * FROM titles WHERE (primary_title = 'Friends') AND (premiered > 1992) LIMIT 5;

So now we know that tt0108778 is the show_title_id for Friends.

Next, we load some lines from the episodes table.

%%sql
SELECT * FROM episodes LIMIT 5;

and we focus attention on the episodes corresponding to Friends.

%%sql
SELECT * FROM episodes WHERE show_title_id = 'tt0108778' LIMIT 5;

Remember, there are 235 such episodes. As we discovered earlier, there are actually 236 episodes of Friends, but in the database, they combined the two episodes from The Last One into one episode listing. So only 235 entries appear in our database.

%%sql
SELECT COUNT(*) FROM episodes WHERE show_title_id = 'tt0108778' LIMIT 5;

Now we pull in the title of the show itself (Friends) from the titles table.

%%sql
SELECT * FROM episodes AS e JOIN titles AS t ON e.show_title_id = t.title_id
WHERE show_title_id = 'tt0108778' LIMIT 5;

Finally, we join the titles again, but this time, we join the titles table to the episodes by the episode_title_id, in other words, we extract the names of the episodes.

%%sql
SELECT * FROM episodes AS e JOIN titles AS t ON e.show_title_id = t.title_id
JOIN titles AS u ON e.episode_title_id = u.title_id
WHERE show_title_id = 'tt0108778' LIMIT 5;