Joining 3+ tables
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 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?
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.
trainersgymsbadgescodemonExercise 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;102045Hint : 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.
-- Write your query below