Sql - Left Join 2 Foreign Keys To 1 Primary Key

I have two tables, Games and Teams. What should my sql statement look like to make a list of games that pulls in the TeamName that is linked to the TeamID1 and TeamID2 fields? I be

Solution 1:

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 joins 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 nulls. It will look like so:

g.GameID    g.TeamID1    g.TeamID2    t1.TeamID    t1.Name    t2.TeamID    t2.Name
----------------------------------------------------------------------------------1131    Lions              3    Bears
       242nullnull2    Tigers
       31null1    Lions           nullnull

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.

Solution 2:

SELECT*FROM Games G
INNERJOIN Teams T1
   ON G.TeamID1 = T1.TeamID
INNERJOIN Teams T2
   ON G.TeamID2 = T2.TeamID

Solution 3:

I don't think you'll need a left outer join unless one of the two teams in a game is optional. Logically to me it seems that both would be required, so your query would look like this:

SELECT *
FROM Games AS G
INNER JOIN Teams AS T1 ON T1.TeamID = G.TeamID1
INNER JOIN Teams AS T2 ON T2.TeamID = G.TeamID2

Solution 4:

If TeamID1 and TeamID2 are NOT NULL, then you want to use an INNER JOIN, otherwise you could use a LEFT JOIN.

Solution 5:

You need to use alias:

SELECT GameID, team1.TeamName, team2.TeamName
FROM Games INNERJOIN teams team1 ON (Games.TeamID1 = team1.TeamID)
           INNERJOIN teams team2 ON (Games.TeamID2 = team2.TeamID)