I am not very sure, what you are exactly looking for.
If you want to just find 'missing links' then
you can use something like this.
SQL> select * from tree;
ID PID
---------- ----------
1
2 1
3 2
4 1
6 5
8 15
9 6
10 3
8 rows selected.
This query gives u the regular hierarchial tree structure..
SQL> select
2 lpad(' ',2*(level-1))||id org,pid from tree
3 connect by prior id = pid
4 start with pid is null;
ORG PID
---------- ----------
1
2 1
3 2
10 3
4 1
And this gives the records that cannot be traced back..or Missing Links
SQL> select id,pid from tree where
2 id not in (
3 select id from tree
4 connect by prior id = pid
5 start with pid is null);
ID PID
---------- ----------
6 5
8 15
9 6
3 rows selected.
] 
Blog
Search
Help
Members
Register
Login
Home
