Joining 3+ tables

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 1 — Welcome to the league

The league database is growing: next to trainers and codemon, there is now a gyms table. Trainers challenge gyms, and every win earns them a badge.

A trainer can earn badges from many gyms, and a gym hands out badges to many trainers. That is a many-to-many relationship, and a single foreign key cannot represent it. The solution is a third table where each row is one link: one trainer, one gym, one date.

badges has two foreign keys, trainer_id and gym_id. Such a table is called a junction table. It can also carry its own information, like earned_on.

One-to-many vs many-to-many

A Codémon has one trainer: a single trainer_id column in codemon is enough (one-to-many). A badge links a trainer and a gym: that needs a row per link, hence the badges table.

Exercise 2 — Chaining joins

To show who earned a badge where, you need three tables: the names are in trainers and gyms, the links are in badges. Each JOIN adds one table and one ON:

SELECT t.name AS trainer, g.name AS gym, bd.earned_on FROM trainers t INNER JOIN badges bd ON bd.trainer_id = t.trainer_id INNER JOIN gyms g ON g.gym_id = bd.gym_id ORDER BY bd.earned_on;

Read it as a path: trainers → badges (through trainer_id) → gyms (through gym_id). The junction table sits in the middle, and the result has one row per badge: 10.

Aliases are not optional anymore

With three tables, t, bd and g keep the query readable, and name exists in two tables, so every column should be qualified.

Exercise 3 — The table in the middle

You want the names of the trainers next to the names of the gyms where they earned a badge. Which table connects trainers and gyms?

Options
• badges, because it holds a trainer_id and a gym_id on each row
• codemon, because Codémons fight in gyms
• No table is needed: trainers has a gym_id column
• gyms itself, through its city column

Hint : Look for the table with two foreign keys.

Exercise 4 — Three tables, two ON

Complete the query so that it lists each badge with the trainer's name and the gym's name.

Hint : The junction table comes first, then the gym's key is matched with the badge's gym_id.

Exercise 5 — Filtering and grouping across three tables

Once the tables are joined, everything you know applies. Here are the badges earned in Azure City, newest first:

SELECT t.name AS trainer, g.name AS gym, bd.earned_on FROM trainers t INNER JOIN badges bd ON bd.trainer_id = t.trainer_id INNER JOIN gyms g ON g.gym_id = bd.gym_id WHERE g.city = 'Azure City' ORDER BY bd.earned_on DESC;

And how many badges each trainer earned in Azure City — the filter is on gyms, the grouping on trainers, and badges links them:

SELECT t.name AS trainer, COUNT(bd.badge_id) AS azure_badges FROM trainers t INNER JOIN badges bd ON bd.trainer_id = t.trainer_id INNER JOIN gyms g ON g.gym_id = bd.gym_id WHERE g.city = 'Azure City' GROUP BY t.name ORDER BY azure_badges DESC, trainer;

The order of INNER JOINs does not change the result

trainers → badges → gyms or gyms → badges → trainers give the same rows. Start from the table you filter or group on: it reads more naturally.

Exercise 6 — Who stores what

Match each table of the league database with its role.

Items to match
• trainers
• gyms
• badges
• codemon
Definitions
• One row per trainer
• One row per gym, with its city and type
• One row per badge earned: a trainer, a gym, a date
• One row per Codémon, pointing to its trainer

Exercise 7 — Count the rows

There are 4 trainers, 5 gyms and 10 badges. How many rows does this query return?

SELECT t.name, g.name FROM trainers t INNER JOIN badges bd ON bd.trainer_id = t.trainer_id INNER JOIN gyms g ON g.gym_id = bd.gym_id;
Options
• 10
• 20
• 4
• 5

Hint : Each badge row links exactly one trainer to exactly one gym.

Exercise 8 — Morgan's collection

Complete the query so that it lists the gyms where Morgan earned a badge, with the date, oldest first.

Hint : Match the trainer's primary key with the badge's trainer_id, then filter on the trainer's name.

Exercise 9 — Gym leaderboard

Professor Pi wants to know which gyms are the busiest.

Write a query that returns, for each gym: its name as gym, its city, and the number of badges it handed out as badge_count. Sort by badge_count descending, then by gym name.

Starter code
-- Write your query below
Back to the course