Yet another question on JOIN, please help

[nathank]

I have been reading up on joins, but I still am a bit unclear how I would write my query to do the following.

 

I have a table named teams

|  ID  |  Teamname        |
|  1    |  The Good Team |
|  2    |  The Bad Team   |

 

And a table named matchups

|  ID  |  Team1  |  Team2  |
|  1    |     1       |      2      |

 

I want to query the matchups table and pull the teamnames from the teams table.

 

"SELECT matchups.team1, teams.teamname as team1, matchups.team2, teams.teamname as team2 FROM matchups join teams on matchups.team1 = teams.id "

 

The join has one team id = to the id in the team table, but what about the other team?

");

[sqlmc]

With this table structure I would try something like this:

 

SELECT A.team1

,B.teamname as team1

,A.team2

,C.teamname as team2

 

FROM matchups A

JOIN teams B

ON A.team1 = B.id

JOIN teams C

ON A.team2 = C.id

 

/*A B and C are table aliases which I used just to cut down on typing*/