asked on

Usin connect to prior for the hirerarchy query and order by

Hi

I have a table where there have relationships

account_id related_account_id relationship_type
---------------------------------------------------
100 110 GP-P
100 120 GP-P
110 130 P-C
120 140 P-C
120 150 P-C
200 210 GP-P
210 220 P-C

I want the data to be returned as first record should be the account_id of grandparent,
next record should be the corresponding parent and next records should be the corresponding child.
Now next record should be the corresponding parent and of the first grand parent and thereafter the corresponding childs of that parent.
Now after these set of records of the first hierarchies next should display the details of second grandparent
ex:
Now the query should return

account_id relationship_type
-------------------------------
100 Grandparent
110 Parent
130 child
120 Parent
140 child
150 child
200 Grandparent
210 Parent
220 child

I know that i should use CONNECT BY PRIOR can someone help me out.
Iam using oracle 9i.

Thanks in advance

Helena Marková

Have you tried something like this ?
Select account_id,relationship_type from <your_table> connect by prior account_id=related_account_id;

Sean Stuber

How are you translating GP-P and P-C to Grandparent, Parent and child?

Is that a typo? Or do you really need your info to translate like that?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial