从同一查询中的同一列中获取两个值[关闭]

Question

I want to know how to get 2 different values from the same column in the same row. I mean, I have my table friends as shown below.

id | source | target
1    1        2
1    1        3

And then I have my users table, with the following values

id | name
1    John
2    Will
3    Mark

I want to know which users are friends, for example, in the first case it would be John and Will are friends.

Answer 1

What you might be looking for is a double join of the same lookup into a single driver:

SELECT
  src.name AS srcName,
  tgt.name AS targetName,
FROM
  friends
  INNER JOIN users AS src ON friends.source=src.id
  INNER JOIN users AS tgt ON friends.target=tgt.id
-- WHERE something?