sql - 如何用一个 Sql 查询可视化两个外键

Question

你好，我对两个外键的可视化名称有疑问。我在 sql 上开发足球 数据库，这是我的 关系图

的一部分

这是我的选择查询:

Select team.Name, comp.Name,comp.Stage,fixDate.Date,news.Description
FROM Fixtures fix
Join Competition comp 
On fix.CompetitionID = comp.ID
Join FixtureDate fixDate
On fix.FixtureDateID=fixDate.ID
Join News news
On news.ID = fix.NewsId
Join Teams team
On fix.AwayTeamID = team.ID;

问题是我不知道如何可视化主队名称和客队名称。我试试

 On fix.AwayTeamID = team.ID AND fix.HomeTeamID = team.ID

但输出为空响应..

这是只有一个团队名称的输出

但我想展示和他的对手客场队。谢谢指教!

Answer 1

为此，您需要两个带有表别名的连接。

Select team1.Name, team2.Name, comp.Name, comp.Stage, fixDate.Date, news.Description
FROM Fixtures fix
Join Competition comp 
On fix.CompetitionID = comp.ID
Join FixtureDate fixDate
On fix.FixtureDateID=fixDate.ID
Join News news
On news.ID = fix.NewsId
Join Teams team1
On fix.HomeTeamID = team1.ID
Join Teams team2
On fix.AwayTeamID = team2.ID;