How to replace FK from table A with the corresponding values in table B?-CodePudding

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