MySQL -从一个表中选择in存储在另一个表中的多行

Question

我很困惑使用JOIN -语句从MySQL数据库中选择数据。以下是表的样子：

Table "Users"
+----+-------+----------+
| ID | Name  | Password |
+----+-------+----------+
| 1  | Mike  | test     |
| 2  | Tony  | test1    |
| 3  | Frank | test2    |
+----+-------+----------+

Table "Games"
+----+-----------+-----------+---------+---------+
| ID | Player1ID | Player2ID | ScoreP1 | ScoreP2 |
+----+-----------+-----------+---------+---------+
| 1  |         1 |         2 |       5 |       2 |
| 2  |         3 |         1 |       2 |       1 |
+----+-----------+-----------+---------+---------+

我想要SELECT * FROM GAMES WHERE Player1ID=1 or Player2ID=1加上用户的名字，而不仅仅是他们的ID。有人能帮我吗？

Answer 1

用不同的别名两次加入users表以区分它们

select g.*, u1.name as player1, u2.name as player2
from games g
join users u1 on u1.id = g.player1id
join users u2 on u2.id = g.player2id