查找普通子女或父母的所有相关记录

Question

我有一个表Identity(id, super_identity_id)和一个表Conversation(id, identity_id)

多个身份可以参考相同的super_identity。

说我有identity A，我想获得所有conversations

1. 谁属于A
2. 谁属于A的super_identity
3. 谁属于与A相同CC_11
的任何identity

我能做的最好的是

select 
    i.id,
    i.super_identity_id
from identities i 
left join identities ii on ii.super_identity_id = i.super_identity_id
where ii.id = 133

，但以上不尊重(2)

有什么想法？

Answer 1

select 
i.id,
i.super_identity_id
from identities i 
inner join identities ii on ii.super_identity_id = i.super_identity_id and ii.id = 133