Multiple SQL Joins on a Single Table / Relationship
There are some times when we have to create multiple joins on a single table, i.e. when we have many elements in a table referencing the same foreign key. It's not a difficult situation to deal with when you've dealt with it before, but can be a little confusing for new DB users. That's why I've created this tutorial - to explain how to properly design and reference your tables in SQL.
Double-Key Example - Teams and Games
An easy example to understand is that of two teams playing a game - say, Football. Each team will have a unique column in a table. Let's take the following simple table as an example of what a team might look like (I put the MySQL-specific syntax in italics. The rest can translate to any ANSI-92 compliant DB):
CREATE TABLE team (
So far so good... this is basic database stuff here. Well, let's move on to games.
Each game consists of two teams and a venue. A very simple way of establishing venue is to reference the home team from the game table. Then, simply make a second reference to the guest team to reference the second participant in the game.
CREATE TABLE game (
Working with the data will be more fun. When joining the two tables, you will have to join twice, and get creative on how to manipulate joins in order to get the data appropriately. Here's a simple selection of all games and the resulting scores.
Finding a single team's schedule involves a union of home games and away games, with the creation of a metadata identifier of venue.
SELECT * FROM (
And that's that! Dig in and have fun!