CODEWITHSUNDEEP
sqlsql-server
Hobroker
Hobroker

Reputation: 380

SQL: How to SELECT different values from a table in another table?

I have two tables

Table 'Teams' has two columns

teamID,
teamName

Table 'Match' has three columns

matchID, 
teamID_1,
teamID_2

... like in image bellow:

How would I construct a select statement which will pull through the Teams.teamName to both the Match.teamID_1 and the Match.teamID_2 based on their respective IDs?

I can do that only with one column or another, but not both:

SELECT Match.matchID, Teams.teamName
    FROM Match
    INNER JOIN Teams ON Match.teamID_1 = Teams.teamID

OR

SELECT Match.matchID, Teams.teamName
    FROM Match
    INNER JOIN Teams ON Match.teamID_2 = Teams.teamID

Upvotes: 2

Views: 75

Answers (1)

Dave Zych
Dave Zych

Reputation: 21887

You can join the same table multiple times. Just alias them (t1 and t2 as shown below):

SELECT 
    Match.matchID, 
    t1.teamName, 
    t2.teamName
FROM Match
    INNER JOIN Teams t1 ON Match.teamID_1 = t1.teamID
    INNER JOIN Teams t2 ON Match.teamID_2 = t2.teamID

Upvotes: 4

Related Questions