mysql - Querying for multiple rows in a table (and its pair in the same table) where one half of the pair is connected to one id in another table

Question

This is a random question that I am trying to figure out in SQL.

Say you have a table whereby the rows come in pairs. So you will have:

same_table
ID Name Family_Id Other_id
1   B.       1.       2 --> This ID is id 2 in the same table.
2.  C.       nil      nil
3   D.       1        4 --> this ID is also the next row in the table.
4   E.       nil      nil

Sorry, this is incredibly convoluted and hard to explain. Essentially I need to get the rows connected to family_id 1 AND the other part of the pair.

So I know SELECT * FROM same_table WHERE family_id=1 --> will get 1 half.

HOW do I write a query where I am looking specifically for the rows connected to a specific family_id and that rows pair.

Sorry... poorly explained but I don't know how else to ask it.

question from:https://stackoverflow.com/questions/66053513/querying-for-multiple-rows-in-a-table-and-its-pair-in-the-same-table-where-one

Answer 1

Well, if the hierarchy is only one level deep (as in your example data and explanation), you can use exists:

select t.*
from same_table t
where t.family_id = :input or
      exists (select 1
              from same_table t2
              where t2.other_id = t.id and t2.famil_id = :input
             );