php - mysql 查询来自多个表的 2 个内部联接

Question

我有以下查询

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 是发送者，我将尝试获取所有接收者的姓名

这应该如何完成？

Answer 1

此查询应显示名称以及在两个场景中的哪一个中找到该名称。

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'
)