CODEWITHSUNDEEP
oracle-databasehierarchical
JonathanPeel
JonathanPeel

Reputation: 783

Oracle hierarchical query join to selection of all decendants

I am trying to retrieve a list of each descendant with each item. I am not sure I am making sense, I will try and explain.

Example Data:

ID | PID
--------
1  | 0
2  | 1
3  | 1
4  | 1
5  | 2
6  | 2
7  | 5
8  | 3

etc...

The desired results are:

ID | Decendant
--------------
1  | 1
1  | 2
1  | 3
1  | 4
...
2  | 2
2  | 5
2  | 6
2  | 7
3  | 3
3  | 8

etc...

This is currently being achieved by using a cursor to move through the data and inserting each descendant into a table and then selecting from them.

I was wondering if there was a better way to do these, there must be a way to right a query that would bring back the desired results.

If any one has ideas, or has figured this out before it would be very appreciated. Ordering is not important, nor is the 1 - 1, 2 -2 reference. It would be cool to have it, but not crucial.

Upvotes: 0

Views: 106

Answers (2)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Here is my attempt! Not sure, if I got you right!

select pid ,connect_By_root(id) as descendant from process
connect by  id = prior pid
union all
select distinct pid,pid from
process
order by pid,descendant

Upvotes: 1

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23737

select connect_by_root(id) as ID, id as Decendant
from table1
connect by prior id = pid
order by 1, 2

fiddle

Upvotes: 1

Related Questions