Retrieve all children and parents of a given record

I want to retrieve all the parents of the given record and then get all the child records of those parent records.

For example: if the Table is something like:

table 1:

Child_Id  | Parent_Id
---------------------
    23           4
    23           5
    4            20
    20           21
    5            12
    12           15
    12           17
    24           30
    39            4

Given the above the table and id is 23 then I have to retrieve,

I tried with connect with prior but it didn't help me:

SELECT * FROM TABLE1 
CONNECT BY PRIOR CHILD_ID = PARENT_ID

Is there any way to first get all the parents of given node and then get all the children of its parents along with siblings?

Solution

You can use the following to get a list of the parents/roots:

select parent_id as parent
  from tbl x
 where not exists (select 1 from tbl y where y.child_id = x.parent_id)

| PARENT |
|--------|
|     15 |
|     21 |
|     17 |
|     30 |

http://sqlfiddle.com/#!4/62c37/18/0

And then for a given root you can run the following:

select 21 as parent,
       listagg(child_id, ' >>> ') within group(order by level) as children
  from tbl
 start with parent_id = 21
connect by prior child_id = parent_id


| PARENT |               CHILDREN |
|--------|------------------------|
|     21 | 20 >>> 4 >>> 23 >>> 39 |

http://sqlfiddle.com/#!4/62c37/35/0

Or if you don't want to bring the children onto one row you can get output similar to what you gave by running:

select 21 as parent,
       lpad(child_id,level*level,' ') as child
  from tbl
 start with parent_id = 21
connect by prior child_id = parent_id


| PARENT |     CHILD |
|--------|-----------|
|     21 |         2 |
|     21 |         4 |
|     21 |        23 |
|     21 |        39 |

http://sqlfiddle.com/#!4/62c37/34/0

I am not sure about running it for all roots in one query but figured I would at least give you that pending another answer.

However if there are always going to be just a few children in each (ie. under 10, say) then this should be doable without relying on CONNECT BY, and using subqueries instead.

As an example of that approach, the following goes 8 levels deep and should work across your whole table as long as there are never more than 8 children (you can add more subs if that is not the case):

with lvl1 as
 (select x.parent_id, x.child_id
    from tbl x
   where not exists (select 1 from tbl y where y.child_id = x.parent_id)),
lvl2 as
 (select x.parent_id, x.child_id
    from tbl x
    left join lvl1
      on x.parent_id = lvl1.child_id),
lvl3 as
 (select x.parent_id, x.child_id
    from tbl x
    left join lvl2
      on x.parent_id = lvl2.child_id),
lvl4 as
 (select x.parent_id, x.child_id
    from tbl x
    left join lvl3
      on x.parent_id = lvl3.child_id),
lvl5 as
 (select x.parent_id, x.child_id
    from tbl x
    left join lvl4
      on x.parent_id = lvl4.child_id),
lvl6 as
 (select x.parent_id, x.child_id
    from tbl x
    left join lvl5
      on x.parent_id = lvl5.child_id),
lvl7 as
 (select x.parent_id, x.child_id
    from tbl x
    left join lvl6
      on x.parent_id = lvl6.child_id),
lvl8 as
 (select x.parent_id, x.child_id
    from tbl x
    left join lvl7
      on x.parent_id = lvl7.child_id)
select  parent_id,
        case when lag(child2,1) over (partition by parent_id order by parent_id) = child2 then null else child2 end as child2,
        case when lag(child3,1) over (partition by parent_id order by parent_id) = child3 then null else child3 end as child3,
        case when lag(child4,1) over (partition by parent_id order by parent_id) = child4 then null else child4 end as child4,
        case when lag(child5,1) over (partition by parent_id order by parent_id) = child5 then null else child5 end as child5,
        case when lag(child6,1) over (partition by parent_id order by parent_id) = child6 then null else child6 end as child6,
        case when lag(child7,1) over (partition by parent_id order by parent_id) = child7 then null else child7 end as child7,
        case when lag(child8,1) over (partition by parent_id order by parent_id) = child8 then null else child8 end as child8
from(
select distinct
       lvl1.parent_id,
       lvl2.parent_id as child2,
       nvl(lvl3.parent_id,lvl2.child_id) as child3,
       nvl(lvl4.parent_id,lvl3.child_id) as child4,
       nvl(lvl5.parent_id,lvl4.child_id) as child5,
       nvl(lvl6.parent_id,lvl5.child_id) as child6,
       nvl(lvl7.parent_id,lvl6.child_id) as child7,
       nvl(lvl8.parent_id,lvl7.child_id) as child8
  from lvl1
  left join lvl2
    on lvl1.child_id = lvl2.parent_id
  left join lvl3
    on lvl2.child_id = lvl3.parent_id
  left join lvl4
    on lvl3.child_id = lvl4.parent_id
  left join lvl5
    on lvl4.child_id = lvl5.parent_id
  left join lvl6
    on lvl5.child_id = lvl6.parent_id
  left join lvl7
    on lvl6.child_id = lvl7.parent_id
  left join lvl8
    on lvl7.child_id = lvl8.parent_id
order by parent_id)

http://sqlfiddle.com/#!4/62c37/50/0

| PARENT_ID | CHILD2 | CHILD3 | CHILD4 | CHILD5 | CHILD6 | CHILD7 | CHILD8 |
|-----------|--------|--------|--------|--------|--------|--------|--------|
|        15 |     12 |      5 |     23 | (null) | (null) | (null) | (null) |
|        17 |     12 |      5 |     23 | (null) | (null) | (null) | (null) |
|        21 |     20 |      4 |     23 | (null) | (null) | (null) | (null) |
|        21 | (null) | (null) |     39 | (null) | (null) | (null) | (null) |
|        30 | (null) | (null) | (null) | (null) | (null) | (null) | (null) |