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,
21
20
4
23 39
15
12
5
23
17
12
5
23
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?
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) |