Join multiple columns from one table to single column from another table

Question

I'm trying to learn how to join multiple columns from one table to a single column from another table.

This is my table structure in its simplest form:

teams

id | team_name |
1  |   teamA   |
2  |   teamB   |
3  |   teamC   |
4  |   teamD   |

trades

id |  team_1 (FK to teams.id)  |  team_2 (FK to teams.id)  |
1  |            1              |              2            |
2  |            3              |              4            |

This is my current SQL which joins trades.team_1 to teams.id:

SELECT teams.team_name AS team1, teams.team_name AS team2, trades.team_1, trades.team_2
FROM teams
JOIN trades ON (trades.team_1 = teams.id);

My question is, how do I create a second join that also joins trades.team_2 to trades.id?

This would mean both trades.team_1 AND trades.team_2 would be joined to trades.id

The results I want to get back would be:

team1  |  team2  |  team_1  |  team_2  |
teamA  |  teamB  |    1     |     2    |
teamC  |  teamD  |    3     |     4    |

score 52 · Accepted Answer · answered Feb 02 '12 at 15:22

52

Like this:

select t1.team_name as team1, t2.team_name as team2, t.team_1, t.team_2
from trades t
inner join teams t1 on t1.id = t.team_1
inner join teams t2 on t2.id = t.team_2;

answered Feb 02 '12 at 15:22

aF.

64,980
43
135
198

5

Jeez, 10 seconds apart and we had damn near the same answer. =) – WWW Feb 02 '12 at 15:23
1

Everybody who responded rocks. Seriously, thanks to each of you for responding so quickly. All answers were similar and all will do the trick. Thanks again folks! – Critter Feb 02 '12 at 15:39

score 14 · Answer 2 · answered Feb 02 '12 at 15:22

14

SELECT t1.team_name AS team1, t2.team_name AS t2, tr.team_1, tr.team_2
FROM trades tr
INNER JOIN teams t1 ON t1.id = tr.team_1
INNER JOIN teams t2 ON t2.id = tr.team_2

answered Feb 02 '12 at 15:22

WWW

9,734
1
29
33

score 6 · Answer 3 · answered Feb 02 '12 at 15:21

Try joining the teams table again but using two different aliases:

SELECT
    teams1.team_name AS team1,
    teams2.team_name AS team2,
    trades.team_1,
    trades.team_2
FROM trades
JOIN teams AS teams1 ON trades.team_1 = teams1.id
JOIN teams AS teams2 ON trades.team_2 = teams2.id

score 2 · Answer 4 · answered Feb 02 '12 at 15:20

2

You need to join twice:

SELECT t1.team_name as team1, t2.team_name as team2, trades.team_t, trades.team_2 
FROM teams t1, teams t2, trades 
WHERE t1.id = trades.team_1 and t2.id = trades.team_2

answered Feb 02 '12 at 15:20

Chris

22,923
4
56
50

Join multiple columns from one table to single column from another table

4 Answers4

Linked