您可以使用子查询（同一查询中的两个子查询）来解决此问题：

select
  gameID, 
  weekNum,
  gameTimeEastern,
  (select teamName from teams where teamID = schedule.homeID) as homeName,
  homeScore,
  (select teamName from teams where teamID = schedule.visitorID) as visitorName,
  visitorScore from schedule;

这并不能从中获取所有列 schedule ，只是一个例子来说明它是如何工作的。如果您需要各种查询（包括 select * ，尽管这不是一个好的实践（除了测试），但是您可以基于上面的查询创建一个视图（使用schedule中的所有列，除了homeid和visitorid之外，这些列将被schedule中的子查询替换） teams 表）。然后，您可以对该视图放置查询—它们的工作方式将类似于您在其中直接包含团队名称的原始表。

听起来你想加入 teams 两次到 schedule .

SELECT s.*,
       th.*,
       ta.*
       FROM schedule s
            INNER JOIN teams th
                       ON s.homeid = th.teamname
            INNER JOIN teams ta
                       ON s.visitorid = ta.teamname 
       WHERE s.gameid = 411;

我猜你想把两个队都放在一排而不是两排。
如果是，则需要加入表 teams 两次。
考虑一下这个演示：http://www.sqlfiddle.com/#!9/bb5e61/1号
此连接将两个团队聚集到一行中：

SELECT s.*,
       t1.teamId as homeId_teamId,
       t1.teamCode as homeId_teamCode,
       t1.teamName as homeId_teamName,
       t2.teamId as visitorId_teamId,
       t2.teamCode as visitorId_teamCode,
       t2.teamName as visitorId_teamName      
FROM Schedule s
JOIN Teams t1 ON s.homeId = t1.teamName
JOIN Teams t2 ON s.visitorId = t2.teamName;

| id | homeId | visitorId | homeId_teamId | homeId_teamCode | homeId_teamName | visitorId_teamId | visitorId_teamCode | visitorId_teamName |
|----|--------|-----------|---------------|-----------------|-----------------|------------------|--------------------|--------------------|
|  1 | Poland |  Colombia |             1 |              PL |          Poland |                2 |                 CO |           Colombia |

但是，您也可以在内部联接上考虑左联接，这将在teams表中没有相关数据的情况下起作用：

SELECT s.*,
       t1.teamId as homeId_teamId,
       t1.teamCode as homeId_teamCode,
       t1.teamName as homeId_teamName,
       t2.teamId as visitorId_teamId,
       t2.teamCode as visitorId_teamCode,
       t2.teamName as visitorId_teamName      
FROM Schedule s
LEFT JOIN Teams t1 ON s.homeId = t1.teamName
LEFT JOIN Teams t2 ON s.visitorId = t2.teamName;

| id |   homeId | visitorId | homeId_teamId | homeId_teamCode | homeId_teamName | visitorId_teamId | visitorId_teamCode | visitorId_teamName |
|----|----------|-----------|---------------|-----------------|-----------------|------------------|--------------------|--------------------|
|  1 |   Poland |  Colombia |             1 |              PL |          Poland |                2 |                 CO |           Colombia |
|  3 | Ya Majka |    Poland |        (null) |          (null) |          (null) |                1 |                 PL |             Poland |
|  2 | Ya Majka |   Rossija |        (null) |          (null) |          (null) |           (null) |             (null) |             (null) |

下面是由示例组成表的脚本

CREATE TABLE Schedule(
  id int,   homeId varchar(20),visitorId varchar(20)
);

INSERT INTO Schedule VALUES
(1, 'Poland', 'Colombia' ),(2,'Ya Majka','Rossija'), 
(3,'Ya Majka','Poland');

CREATE TABLE Teams( 
  teamId int, teamCode varchar(10), teamName varchar(20)
);

INSERT INTO Teams VALUES
(1, 'PL', 'Poland' ),(2,'CO','Colombia'),(3,'US','United States');