CODEWITHSUNDEEP
sqloracle-databasehierarchy
Jörg Henke
Jörg Henke

Reputation: 123

oracle ancestors in hierarchical queries

Given table T_Person (name, parent) containing data

+--------+--------+
| name   | parent |
+--------+--------+
| john   | peter  |
| peter  | ronald |
| ronald | george |
| george |        |
+--------+--------+

one can find relations using this query:

select name, parent, LEVEL
from T_Person
connect by prior name = parent
start with parent is null;

the result would be:

+--------+--------+-------+
| name   | parent | LEVEL |
+--------+--------+-------+
| john   | peter  | 4     |
| peter  | ronald | 3     |
| ronald | george | 2     |
| george |        | 1     |
+--------+--------+-------+

That's fine so far. But I want to have a result containing all relations that looks this way:

+--------+--------+----------------+
| name   | parent | relation_level |
+--------+--------+----------------+
| john   | peter  | 1              |
| peter  | ronald | 1              |
| ronald | george | 1              |
| john   | ronald | 2              |
| peter  | george | 2              |
| john   | george | 3              |
+--------+--------+----------------+

(relation_level: 1 = father, 2 = grand father, 3 = grand grand father and so on)

Is there a fast oracle way to receive this result, other than select on the whole table for each relation level?

Upvotes: 2

Views: 1825

Answers (2)

A.B.Cade
A.B.Cade

Reputation: 16905

Or you can remove the start with clause

SELECT name, ancestor AS parent, l
FROM
(
  SELECT name, parent, LEVEL-1 l, connect_by_root name ancestor
    FROM T_Person
 CONNECT BY PRIOR name = parent
) t
WHERE t.ancestor <> t.name

Here is a sqlfiddle demo

Upvotes: 3

Gaurav Soni
Gaurav Soni

Reputation: 6336

create table ancestor (name varchar2(200), parent varchar2(200));

insert into ancestor values ('john','peter');
insert into ancestor values ('peter','ronald');
insert into ancestor values ('ronald','george');
insert into ancestor values ('george',null);

 select  name,parent,relation_level from 
(

 SELECT CONNECT_BY_ROOT name name, parent,level relation_level
 FROM ancestor
 CONNECT BY name = PRIOR parent
  START WITH name IN (SELECT name FROM ancestor)
 )
where parent is not null;

SQLFIDDLE

Upvotes: 2

Related Questions