php - 带有来自多个表的2个innerjoins的mysql查询

我有以下查询

SELECT name from login
(INNER JOIN connections on login.id = connections.receiver) AND (INNER JOIN connections on login.id = connections.sender)
WHERE (((connections.sender = '33') AND (connections.status = 'active'))
AND ((connections.receiver = '33') AND (connections.status = 'active')))

如果我的session_id（33）是接收者，我试图获取所有发送者的名字，如果我的session_id是发送者，则试图获取所有接收者的名字

应该怎么做？

最佳答案

该查询应显示名称以及在两种情况下找到该名称的位置。

select distinct name, status from (
  select name , 'Receiver' status
  from login, connections
  where login.id = connections.receiver
  and connections.receiver = '33'
  and connections.status = 'active'
  union
  SELECT name , 'Sender' status
  from login, connections
  where login.id = connections.sender
  and connections.sender= '33'
  and connections.status = 'active'
)