SQL - Left join 2 foreign keys to 1 primary key

mysql, sql

Solution

I often see folks struggle with the idea of joining a table unto itself or multiple times in the same query (as it were here). Once mastered, it's a great technique to use on tables that have a lot of relationships between rows (such as a list of teams that have to play each other!). As others have pointed out, you need to use two `inner join`s to accomplish this:

select
    *
from
    games g
    inner join teams t1 on
        g.teamid1 = t1.teamid
    inner join teams t2 on
        g.teamid2 = t2.teamid

So, if your `games` table looks like this:

GameID    TeamID1    TeamID2
----------------------------
     1          1          3
     2          4          2
     3          2          1

You will get the result set of:

g.GameID    g.TeamID1    g.TeamID2    t1.TeamID    t1.Name    t2.TeamID    t2.Name
----------------------------------------------------------------------------------
       1            1            3            1    Lions              3    Bears
       2            4            2            4    Oh My              2    Tigers
       3            2            1            2    Tigers             1    Lions

Of course, I would alias these columns in the `select` statement, if I were me, for usability's sake:

select
    g.GameID,
    t1.Name as Team1,
    t2.Name as Team2
from
    ...

This way, columns can be named appropriately, instead of having the `t1` and `t2` columns share the same names.

Now, to address the confusion about what a `left join` is. You see, a `left join` will take all of the rows from the first (or left) table, and then match up any rows on the join condition to the second (or right) table. For any rows from the left table, you will get `null` in all of the columns on the `right` table.

Delving into an example, let's say that somebody put in a `null` for `TeamID2` on one of the rows for whatever reason. Let's also say that a team of `TeamID` 4 used to exist, but doesn't any more.

GameID    TeamID1    TeamID2
----------------------------
     1          1          3
     2          4          2
     3          1       null

Now, let's take a look at what a `left join` would be in terms of the query:

select
    *
from
    games g
    left join teams t1 on
        g.teamid1 = t1.teamid
    left join teams t2 on
        g.teamid2 = t2.teamid

Logically, this will grab all of our `games`, and then match them up to the respective `teams`. However, if a `TeamID` doesn't exist, we'll get `null`s. It will look like so:

g.GameID    g.TeamID1    g.TeamID2    t1.TeamID    t1.Name    t2.TeamID    t2.Name
----------------------------------------------------------------------------------
       1            1            3            1    Lions              3    Bears
       2            4            2         null    null               2    Tigers
       3            1         null            1    Lions           null    null

Therefore, a `left join` will only be necessary if a team is optional.

In your case, you'll be using an `inner join` to join a table multiple times. This is a very common practice and is rather useful. It avoids some of the pitfalls of subqueries (especially on MySQL), while allowing you to grab data from the table for intratable comparisons. This is markedly useful when trying to find the order of something, or related rows.

Anyway, I hope this very rambling answer helps out somebody somewhere.

Problem

Original source