必须对不同列使用两次的内联接表

Question

我有表格users：

id         name     
-----      -----    
1          Mark     
2          John    
3          Paul     
4          Dave    
5          Chris   

和表matches：

id    sender    receiver   matched
--    ------    --------   -------
1        2         5          2
2        1         3          0
3        1         2          0

因此，约翰发送了 1 个匹配请求，他的匹配被接受。 约翰也收到了1场比赛，但仍然不被接受。

我正在使用此代码通过用户的ID查找用户的名称

SELECT matches.sender as sender,matches.receiver as receiver,users.name as `userName`,
users.user as `userName2` FROM `matches`
INNER JOIN `users` ON matches.sender = users.id
WHERE '$id' IN (sender,receiver) AND matched='2'

但它只拉出发件人的用户名。当我打字时

...ON matches.sender = users.id AND matches.receiver = users.id .... 

结果为 0

所以我需要拿出两个人的名字。

Answer 1

我认为你想要两个连接：

select m.*, us.name as sender_name, ur.name as receiver_name
from matches m join
users us
on m.sender = us.id join
users ur
on m.receiver = ur.id
where 2 in (m.sender, m.receiver) and
m.matched = 2;