I have 2 tables.
Table 1: Teams
TeamsId__|__TeamName__|_TeamAbv__|__TeamLogo__|
| | | |
1 | Eagles | PHI | eagles.png |
_________|____________|__________|____________|
2 | Bills | BUF | bills.png |
_________|____________|__________|____________|
Continues for 32 teams
Table 2: Schedule
_GameId__|__HomeTeam__|_AwayTeam__|____Week____|
| | | |
1 | 1 | 2 | Week1 |
_________|____________|___________|____________|
2 | 7 | 12 | Week1 |
_________|____________|________ __|____________|
The HomeTeam and the AwayTeam numbers are the assigned TeamsId in the Teams Table
I would like to write a query that display the TeamName for each the Home and Away teams in a separate column in 1 table
What I have tried:
Would like to convert this code into 1 table 2 columns:
SELECT Teams.TeamName AS HomeTeam
FROM Teams
JOIN Schedule ON Teams.TeamsId = Schedule.HomeTeam
SELECT Teams.TeamName AS AwayTeam
FROM Teams
JOIN Schedule ON Teams.TeamsId = Schedule.AwayTeam
First Attempt: Error Conversion failed when converting the nvarchar value 'Eagles' to data type int.
SELECT Teams.TeamName AS HomeTeam, Teams.TeamName AS AwayTeam
FROM Teams
JOIN Schedule
ON Teams.TeamName = (SELECT Teams.TeamsId FROM Schedule WHERE Teams.TeamsId = Schedule.AwayTeam)
WHERE Teams.TeamsId = Schedule.HomeTeam