SQL:如何从同一个表中选择两个 ID 作为两个名称？

Question

我是 SQL 的新手，正在尝试处理连接和引用。

数据库看起来像这样:

玩家

 id | name
----+-------
  1 | john
  2 | tim
  3 | frank
  4 | tony
  5 | mark

匹配

 id | winner | loser
----+--------+-------
  1 |      2 |     4
  2 |      3 |     4
  3 |      4 |     5
  4 |      1 |     4

我想得到这个结果:

 id | winner | loser
----+--------+-------
  1 |    tim |  tony
  2 |  frank |  tony
  3 |   tony |  mark
  4 |   john |  tony

到目前为止我可以做到这一点:

select matches.id, players.name as winner, players.name as loser
  from matches, players 
  where winner = players.id;

但是如果我添加 and loser = players.id，它不会返回任何行。

我也试过这个:

select matches.id, players.name as winner, players.name as loser
  from matches
  join players on matches.winner = players.id
  join players on matches.loser = players.id;

这会产生 错误:多次指定表名“玩家”。

我用谷歌搜索了一个小时，可以找到很多几乎得到我想要的结果的案例，但我仍然无法弄清楚如何用相同的两个名称替换两个 ID表。

Answer 1

你快到了:

select matches.id, w.name as winner, l.name as loser
 from matches
 join players w on matches.winner = w.id
 join players l on matches.loser = l.id;