Sql - Joining 3+ tables
Code Life
By Pierre Marsaa
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.
Loading data...
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.
Loading data...
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.
Verify