内联两次

我有以下表格：

游戏：

id | tournament | player1 | player 2
1  | 1          | 1       | 2
2  | 1          | 3       | 4

玩家们

id | name
1  | Johnson
2  | Smith

比赛

id | name
1  | Tournament 1

现在，我要提取“游戏”表中的所有信息。我用过：

SELECT t.name, g.player1, g.player2
FROM tournaments AS t
INNER JOIN games AS g ON g.tournament = t.id

这可用于提取比赛的信息-但我也在寻找与选手相同的信息。我唯一能找到的解决方案是在循环后执行另一个SQL从播放器中提取信息：

 SELECT * FROM players where id = player1variable

这是最好的解决方案吗？是否可以在第一个SQL中包含此信息？

您可以通过players两次加入游戏桌来完成此操作，每个玩家一次：

SELECT t.name, p1.*, p2.*
FROM tournaments AS t
INNER JOIN games AS g ON g.tournament = t.id
INNER JOIN players AS p1 ON p1.id = g.player1
INNER JOIN players AS p2 ON p2.id = g.player2

输出：

name            id  name    id  name
Tournament 1    1   Johnson 2   Smith

dbfiddle上的演示

请注意，这将为您提供具有相同名称的列，这可能会导致应用程序出现问题。您可以通过使用列别名来解决此问题：

SELECT t.name, p1.name AS player1, p2.name AS player2
FROM tournaments AS t
INNER JOIN games AS g ON g.tournament = t.id
INNER JOIN players AS p1 ON p1.id = g.player1
INNER JOIN players AS p2 ON p2.id = g.player2

输出：

name            player1     player2
Tournament 1    Johnson     Smith

dbfiddle上的演示