mysql - 获取 friend 姓名的SQL表达式

Question

我正在使用 SQL 创建一个交友网络功能。我使用了以下代码:

SELECT relationship.requester, 
       relationship.requested, 
       user.firstname, 
       user.lastname 
  FROM relationship 
  JOIN user ON (user.guid = relationship.requester
           OR user.guid = sqrelationship.requested) 
 WHERE relationship.requester = {$userid} 
    OR relationship.requested = {$userid} 
   AND relationship.approved = 1

SQL 将返回一个表，其中包含用户发起或接收的已批准关系的 ID。但是，user.firstname 和 user.lastname 只返回一组结果。我怎样才能让这个表达式能够同时获得请求者和被请求者的名字？

Answer 1

很难从你没有指定表格设计的问题中分辨出来，但我猜是这样的:

select relationship.requester, relationship.requested, 
       requester.firstname, requester.lastname,
       requested.firstname, requested.lastname,
from relationship
left join user as requester on user.guid = relationship.requester 
left join user as requested on user.guid = relationship.requested
where relationship.requester = {$userid}
  and relationship.approved = 1