表"Teams"
TEAM ID | TEAMNAME |
---|---|
1 | NAME-1 |
2 | NAME-2 |
3 | NAME-3 |
... | ... |
表"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 |
我想要连接这两个表,所以我得到了下表:
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 |
为了清楚起见,我们的目标是用表Teams中的相应值(团队名称)替换FK,并保留表B的列名.我try 了以下方法的许多变体,但效果不是很好:
SELECT
[GameScores].[GameID],
[Teams].[TeamName],
[Teams].[TeamName],
[GameScores].[HomeTeamScore],
[GameScores].[AwayTeamScore]
FROM [GameScores]
INNER JOIN [Teams] ON [GameScores].[HomeTeam]=[Teams].[TeamID]