MySQL JOIN multiple columns with corresponding ID

Question

I have two tables, one with names of teams, the other holds various selections made by users, each of which is represented by the team's team_id. I want to be able to display the actual team names for each, but can't figure out how the join would work.

Table 1

user_id(int), selection1(int), selection2(int), selection3(int)

Table 2

team_id(int), team_name(varchar)

Gordon Linoff · Accepted Answer · 2013-07-29 03:20:59Z

1

You need three joins:

select u.user_id, t1.team_name as team_name1, t2.team_name as team_name2,
       t3.team_name as team_name3
from users u left outer join
     teams t1
     on u.selection1 = t1.team_id left outer join
     teams t2
     on u.selection2 = t2.team_id left outer join
     teams t3
     on u.selection3 = t3.team_id;

answered Jul 29, 2013 at 3:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

twyntee_trey Over a year ago

what do t1, t2, and t3 represent? there's only one teams table.

Gordon Linoff Over a year ago

@KyleHagler . . . They are table aliases, and an important part of the SQL language. For instance, they let you use the same table several times in a query -- when you need it.

Collectives™ on Stack Overflow

MySQL JOIN multiple columns with corresponding ID

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related