Table "Teams"
TEAM ID | TEAMNAME |
---|---|
1 | NAME-1 |
2 | NAME-2 |
3 | NAME-3 |
... | ... |
Table "GameScores"
GameID | HomeTeam(FK) | AwayTeam(FK) | HomeTeamScore | AwayTeamScore |
---|---|---|---|---|
1 | 1 | 2 | 30 | 20 |
2 | 2 | 3 | 35 | 30 |
3 | 3 | 4 | 40 | 30 |
4 | 4 | 5 | 50 | 60 |
I want to join the two tables so I get the following table:
GameID | HomeTeam(FK) | AwayTeam(FK) | HomeTeamScore | AwayTeamScore |
---|---|---|---|---|
1 | NAME-1 | NAME-2 | 30 | 20 |
2 | NAME-2 | NAME-3 | 35 | 30 |
3 | NAME-3 | NAME-4 | 40 | 30 |
4 | NAME-4 | NAME-5 | 50 | 60 |
Just to be clear, the goal is to replace the FK with their corresponding values (team names) from table Teams and also to retain table B column names. I've tried a lot of variations of the following, but it doesn't quite work:
SELECT
[GameScores].[GameID],
[Teams].[TeamName],
[Teams].[TeamName],
[GameScores].[HomeTeamScore],
[GameScores].[AwayTeamScore]
FROM [GameScores]
INNER JOIN [Teams] ON [GameScores].[HomeTeam]=[Teams].[TeamID]
CodePudding user response:
Joining the table Teams twice with a different alias each time
SELECT GameScores.GameID
,TeamH.TeamName
,TeamA.TeamName
,GameScores.HomeTeamScore
,GameScores.AwayTeamScore
FROM GameScores
INNER JOIN Teams TeamH ON GameScores.HomeTeam=TeamH.TeamID
INNER JOIN Teams TeamA ON GameScores.AwayTeam=TeamA.TeamID
CodePudding user response:
I don't use SQL Server but something like generally works on most SQL platforms
SELECT GameScores.GameID, HomeTeam, AwayTeam, HomeTeamScore, AwayTeamScore
FROM (SELECT GameID, TeamID AS HomeTeam FROM GameScores
INNER JOIN Teams ON HomeTeam(FK)=TeamID),
(SELECT GameID, TeamID as AwayTeam FROM GameScores
INNER JOIN Teams ON AwayTeam(FK)=TeamID),
GameScores