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 (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
) TYPE=InnoDB;

So far so good... this is basic database stuff here.  Well, let's move on to games.

Two References

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 (
    id INT NOT NULL AUTO_INCREMENT,
    home_team_id INT NOT NULL REFERENCES team(id),
    guest_team_id INT NOT NULL REFERENCES team(id),
    home_score INT NULL,
    guest_score INT NULL,
    game_date DATE NOT NULL,
    PRIMARY KEY (id)
) TYPE=InnoDB;

Double Jointed 

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. 

SELECT
    g.*,
    t1.name AS home_team_name,
    t2.name AS guest_team_name
FROM
    game AS g
    INNER JOIN team AS t1 ON g.home_team_id = t1.id
    INNER JOIN team AS t2 ON g.guest_team_id = t2.id;
ORDER by game_date DESC 

Back-Reference

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 (
    SELECT
        t1.*,
        g.id AS game_id,
        g.game_date,
        t2.id AS opponent_id,
        t2.name AS opponent_name,
        'home' AS venue
    FROM
        game AS g 
        INNER JOIN team AS t1 ON g.home_team_id = t1.id
        INNER JOIN team AS t2 ON g.guest_team_id = t2.id
    UNION ALL
    SELECT
        t1.*,
        g.id AS game_id,
        g.game_date,
        t2.id AS opponent_id,
        t2.name AS opponent_name,
        'away' AS venue
    FROM
        game AS g 
        INNER JOIN team AS t1 ON g.guest_team_id = t1.id
        INNER JOIN team AS t2 ON g.home_team_id = t2.id
) AS schedule
WHERE schedule.id = 1
ORDER BY schedule.game_date ASC

And that's that!  Dig in and have fun!