mysql - SQL:创建 Home & Away 查询

Question

我有以下两个产生以下结果的查询:

mysql>  select f.fixturematchday, f.fixtureid, t.teamname as homeTeam
          from straightred_fixture as f,
               straightred_team as t
          where f.fixturematchday = 15 and f.hometeamid = t.teamid;

+-----------------+-----------+-------------+
| fixturematchday | fixtureid | homeTeam    |
+-----------------+-----------+-------------+
|              15 |    364393 | Hull        |
|              15 |    364394 | Leicester   |
+-----------------+-----------+-------------+
2 rows in set (0.00 sec)

mysql> select f.fixturematchday, f.fixtureid, t.teamname as awayTeam 
         from straightred_fixture as f,
              straightred_team as t 
         where f.fixturematchday = 15 and f.awayteamid = t.teamid;
+-----------------+-----------+----------------+
| fixturematchday | fixtureid | awayTeam       |
+-----------------+-----------+----------------+
|              15 |    364393 | Crystal Palace |
|              15 |    364394 | Man City       |
+-----------------+-----------+----------------+
2 rows in set (0.00 sec)

但我想返回:

+-----------------+-----------+-------------+----------------+
| fixturematchday | fixtureid | homeTeam    | awayTeam       |
+-----------------+-----------+-------------+----------------+
|              15 |    364393 | Hull        | Crystal Palace |
|              15 |    364394 | Leicester   | Man City       |
+-----------------+-----------+-------------+----------------+

我假设需要某种连接。

Answer 1

您已经在这些查询中执行JOIN。您加入 straightred_team 是为了获得团队名称。您可以JOIN 该表两次，一次用于f.hometeamid，一次用于f.awayteamid。

_{注意:可以多次 JOIN 同一个表，也可以 JOIN 一个表到它自己。}

select f.fixturematchday, f.fixtureid,
       th.teamname as homeTeam, ta.teamname as awayTeam
from straightred_fixture as f,
     straightred_team as th,
     straightred_team as ta
where f.fixturematchday = 15
  and f.hometeamid = th.teamid
  and f.awayteamid = ta.teamid;

您也可以使用 JOIN .. ON 语法编写此查询:

SELECT f.fixturematchday, f.fixtureid,
       th.teamname as homeTeam, ta.teamname as awayTeam
FROM straightred_fixture as f
JOIN straightred_team as th ON f.hometeamid = th.teamid
JOIN straightred_team as ta ON f.awayteamid = ta.teamid
WHERE f.fixturematchday = 15