Something like this:
SELECT
Schd.ScheduleId,
Schd.Week,
Home1.TeamName As GameHome_1,
Away1.TeamName As GameAway_1,
Home2.TeamName As GameHome_2,
Away2.TeamName As GameAway_2
FROM
Schedule Schd
LEFT JOIN Teams Home1
ON Home1.TeamsId = Schd.GameHome_1
LEFT JOIN Teams Away1
ON Away1.TeamsId = Schd.GameAway_1
LEFT JOIN Teams Home2
ON Home2.TeamsId = Schd.GameHome_2
LEFT JOIN Teams Away2
ON Away2.TeamsId = Schd.GameAway_2
ORDER BY
Schd.Week
;
NB: If you're guaranteed to always have all four team IDs set on every schedule row, you can use
INNER JOIN
. If there's any possibility that one or more of the team IDs will
not be set, then you need to use
LEFT JOIN
.