Need help with Connect by prior SQL statement 1

[KrivJohn]

Hi,

I am not so well versed in Oracle analytic functions and am having trouble with a sql.

I have a tree table storing heirarchies upto 4 levels.

create table FAMILY_TREE
(
TREE_ID NUMBER not null,
PARENT_ID NUMBER,
NAME VARCHAR2(100)
);

I know that using a CONNECT BY PRIOR, I can find out who the previous ancestar is.

select
f.tree_id TreeId,
f.parent_id ParentId,
f.Name Name
from family_tree f
connect by
prior f.tree_id = f.parent_id
start with f.parent_id is null

I need to display all the ancestars in a single line (in different columns), which is where I am stumped.

I need the output as :
Joe Jack Harry Rick Ned

Bascially, I need to display in a single row, all the ancestars.

I tried to use joins but it was very very slow due to the fact that I had to perform multiple joins.

Can anyone please point me in the right direction?



Data:
insert into FAMILY_TREE (TREE_ID, PARENT_ID, NAME)
values (1, null, 'Joe');
insert into FAMILY_TREE (TREE_ID, PARENT_ID, NAME)
values (2, 1, 'Jack');
insert into FAMILY_TREE (TREE_ID, PARENT_ID, NAME)
values (3, 2, 'Harry');
insert into FAMILY_TREE (TREE_ID, PARENT_ID, NAME)
values (4, 3, 'Rick');
insert into FAMILY_TREE (TREE_ID, PARENT_ID, NAME)
values (5, 4, 'Ned');

 

[PHV]

SELECT A.name Level0, B.name Level1, C.name Level2, D.name Level3, E.name Level4
FROM family_tree A
LEFT JOIN family_tree B ON A.tree_id = B.parent_id
LEFT JOIN family_tree C ON B.tree_id = C.parent_id
LEFT JOIN family_tree D ON C.tree_id = D.parent_id
LEFT JOIN family_tree E ON D.tree_id = E.parent_id
WHERE A.parent_id IS NULL

Be sure that both tree_id and parent_id are indexed.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AlexCuse]

One of these forums is where you should ask

forum1177
forum759
forum186

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

How deep are these going to go? PHV's suggestion will work, but it might not depending on how many you end up needing to show in a row (you need as many joins as the max number of ancestors you could possibly encounter).

I don't know Oracle at all, but if it is possible you might consider a recursive common table expression for this. [google]recursive common table expression[/google]

Ignorance of certain subjects is a great part of wisdom

 

[PHV]

Alex, the OP said:
I have a tree table storing heirarchies upto 4 levels

 

[AlexCuse]

Ignorance of certain subjects is a great part of wisdom