Learn SQL: Multiple joins
More than two tables
"Who scored, against whom, and for which team?" Yasmine wants a top scorers list for the club magazine. It's spread across four tables.
JOIN after JOIN
You can chain as many JOINs as you like. Each JOIN gets its own ON. Here every goal gets the scorer's name and the opponent of that match.
Follow the keys: goals points at players and at matches.
SELECT p.name, g.minute, m.opponent FROM goals g JOIN players p ON g.player_id = p.id JOIN matches m ON g.match_id = m.id| name | minute | opponent |
|---|---|---|
| Yannick Diallo | 12 | SK Molenhoek |
| Bram De Smet | 55 | SK Molenhoek |
| Yannick Diallo | 78 | SK Molenhoek |
| Charlotte Maes | 30 | Rapid Heidebos |
| Nora Aerts | 71 | Rapid Heidebos |
| Lars Willems | 8 | KV De Vaart |
| Lars Willems | 44 | KV De Vaart |
| Mila Jacobs | 60 | KV De Vaart |
A chain of keys
Sometimes you have to go through a table in between. A goal doesn't know which team it was for, but it does know which match. And the match knows which team played. So you follow the chain goals → matches → teams.
And you can combine it all with what you already know, like GROUP BY.
SELECT t.name, COUNT(*) AS goals FROM goals g JOIN matches m ON g.match_id = m.id JOIN teams t ON m.team_id = t.id GROUP BY t.name| name | goals |
|---|---|
| Men A | 10 |
| U11 | 2 |
| U13 | 7 |
| U15 | 4 |
| Women A | 6 |
Try it yourself
Make the top scorers list. For each player, show the name, the team name and the number of goals.
Ready for more?
The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson