find all parent or child in table-CodePudding

I have table like below:

parent_id	child_id
2	5
3	9
3	5
8	9
8	33
8	18
8	17
9	22
22	4
22	11
38	9

I want query or procedure in sql that return all related value with on id (direct or in direct) for example if user input 9 output is: 38,8,3,22,9,17,33,18,5,11,4

my current query is:

Formatted on 9/15/9091 9:59:59 AM (QP5 v5.390) */
WITH
Parents
AS
    (SELECT YT.child_id, YT.PARENT_ID
       FROM ARTAPAY_TEMP.REL_TEST YT
      WHERE YT.child_id = 6
     UNION ALL
     SELECT YT.CHILD_ID, YT.PARENT_ID
       FROM ARTAPAY_TEMP.REL_TEST  P
            JOIN ARTAPAY_TEMP.REL_TEST YT ON P.PARENT_ID = YT.CHILD_ID),
Children
AS
    (SELECT YT.CHILD_ID, YT.PARENT_ID
       FROM ARTAPAY_TEMP.REL_TEST YT
      WHERE YT.CHILD_ID = 6
     UNION ALL
     SELECT YT.CHILD_ID, YT.PARENT_ID
       FROM ARTAPAY_TEMP.REL_TEST  C
            JOIN ARTAPAY_TEMP.REL_TEST YT ON C.CHILD_ID = YT.PARENT_ID)
SELECT child_id, Parent_ID
FROM Parents P
UNION ALL
SELECT child_ID, Parent_ID
fROM Children C
 WHERE C.child_ID != 6

CodePudding user response：

First of all your query is not recursive. In a recursive CTE the query references itself, e.g. with cte (x) as (select x from t union all select x 1 from **cte** join t on t.x = cte.x 1.

Then you are not looking for an ID's parents and children as the title suggests, but for the whole family tree including all siblings. You want ID 33 in the results which is neither parent nor child to ID 9, but both ID 9 and ID 33 have the same parent ID 8.

with cte (id) as
(
  select parent_id from mytable where 9 in (child_id, parent_id)
  union all
  select case when c.id = t.parent_id then t.child_id else t.parent_id end
  from mytable t
  join cte c on c.id in (t.child_id, t.parent_id)
)
cycle id set is_cycle to 1 default 0
select distinct id from cte;

Demo: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=8e4ec2500ffbfcc886a15635dfa66d6d