child,chid of child ,child of child of child

.........
child |parent|
..........
a1 |a |
..........
a2 |a |
..........
a11 |a1 |
..........
b1 |b |
..........
b11 |b1 |
..........
b111 |b1 |
..........

Here is my table
I want to get all childs of "a" ie {a1,a11,a2}
I mean recursively
ie child,chid of child ,child of child of child ......etc up to any extentet the table contain
How can i for mulate the select querry
?

It depends which type of database you are using. If it is SQL Server 2005 then you can use CTE but this isn't able in 2000. Have a look atthis article for more info.

|||

i am using SQL Server 2000

|||

There are various methods you can use in 2000. Most of them are listedhere.