SQL

[Suresh Angelo]

Hi
I have a database table with two columns, Nname,NParent. Data are like;
Nname ,NParent
n1 ,origin
n2 ,n1
n3 ,n2
n4 ,n2

So what I want is a SQL statement , given a particular Nname( say n2) you should return all Nnames which has n2 as the parent; i.e n2,n3,n4.
I think this is something to do with a co-related subquerry. Please give me this urgent help.

thanks

[Suresh Angelo]

Hi
Sorry about this correction.
Actually the querry should give every level after the first level I mentioned in the original posted question. i.e also it should look for Nname's that have n3,n4 as their parent and so on..
thanks

[Green_Beret]

Try this :

SELECT lpad(' ',2 * (LEVEL - 1)) || Nname
FROM table_name
START WITH Nname = 'n1'
CONNECT BY PRIOR Nname = NParent

O/P:


n1
n2
n3
n4



Regards,
The Beret.

[Iouri]

>>SQL statement , given a particular Nname( say n2) you should return all Nnames which has n2 as the parent

select nname, nparent from yourtable where nparent = 'n2'

Iouri Boutchkine
iouri@hotsheet.com