two foreign keys to same primary key select statement MYSQL

Question

first Table "teams" has TeamCode(varchar 5) and TeamName (varchar 20) second Table "season" has homeTeam (varchar 5) , Team2 (varchar 5), Gameday (date)

homeTeam & Team2 are FKs that are connected to TeamCode PK

table: teams

| TeamCode | TeamName |
|:-----------:|:--------------|
| 1 | USA |
| 2 | UK |
| 3 | JAPAN |

---

table: season

each team plays the other once as a home team

| 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|

---

I want a query that would display the Team names and the day they will play together

So it should look like

HomeTeam Name |   Team2 Name  |  Gameday

Answer 1

Try this

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

Answer 4

SQL Server:

SELECT t1.TeamName AS [HomeTeam Name],
       t2.TeamName AS [Team2 Name],
       s.Gameday
  FROM Season s
  JOIN Teams t1 ON t1.TeamCode = s.homeTeam -- or s.Team1 (unclear in your post which is the right column name)
  JOIN Teams t2 ON t2.TeamCode = s.Team2

MySQL:

SELECT t1.TeamName AS `HomeTeam Name`,
       t2.TeamName AS `Team2 Name`,
       s.Gameday
  FROM Season s
  JOIN Teams t1 ON t1.TeamCode = s.homeTeam -- or s.Team1 (unclear in your post which is the right column name)
  JOIN Teams t2 ON t2.TeamCode = s.Team2