两个外键指向同一个主键选择语句MYSQL

Question

第一表“团队”有TeamCode(varchar 5)和TeamName (varchar 20)，第二表有homeTeam (varchar 5)，Team2 (varchar 5)，Gameday (日期)。

homeTeam & Team2是连接到TeamCode PK的FKs

表:小组

TeamCodeTeamName1USA2UK3JAPAN

表:季节

每支球队都会以主场的形式与对方比赛一次。

| Team1 | Team2 |Gameday
|:-----:|:------|:------|  
| 1     | 2     | 7  jan|
| 1     | 3     | 14 jan|
| 2     | 1     | 21 jan|  
| 2     | 3     | 28 jan|  
| 3     | 1     | 4  feb|
| 3     | 2     | 11 feb|

我想要一个查询，显示球队的名字和他们一起比赛的日子。

所以看起来应该是

HomeTeam Name |   Team2 Name  |  Gameday

Answer 1

尝尝这个

SELECT
     T1.Name As Host ,
     T2.Name As Guest,
     S.Date 
FROM [dbo].[Season] as S
Inner Join [dbo].[Team] as T1 on S.HostTeam = T1.ID
Inner Join [dbo].[Team] as T2 on S.GuestTeam = T2.ID

Answer 2

SELECT
    ht.TeamName,
    at.TeamName,
    s.GameDay
FROM
    teams AS ht
    INNER JOIN season AS s ON ht.TeamCode = s.Team1
    INNER JOIN teams AS at ON s.Team2 = at.TeamCode

Answer 3

declare @t table (Teamid int,name varchar(10))
insert into @t (Teamid,name)values (1,'usa'),(2,'uk'),(3,'japan')
declare @tt table (Team1 int,Team2 int,gameday varchar(10))
insert into @tt(Team1,Team2,gameday)values (1,2,'7 jan'),(1,3,'14 jan'),(2,1,'21 jan'),(2,3,'28 jan'),(3,1,'4 feb'),(3,2,'11 feb')

select (select name from @t where Teamid = t.Team1) Home,
       (select name from @t where Teamid = t.Team2) Guest,
       gameday
from @tt t