sql server programming: The adjacency tree/hierarchy model requires recursion, try using the nested
model (see: Joe Celko, Trees and Hierarchies).

Have you looked at the example I suggested last time you posted this question?

http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.programming&mid=d6ff47e8-9013-483d-b88c-2f72e66bccfc

Hi,
I have an employee table having fields empid and managerid.
I need a query which returns all the children once the parentid is given.

Here is the script

create table employee (empid int , parentID int )
GO
insert into employee select 1,null
insert into employee select 2,1
insert into employee select 3,1
insert into employee select 4,3
insert into employee select 5,3
insert into employee select 6,4
insert into employee select 7,4
insert into employee select 8,5
insert into employee select 9,5
insert into employee select 10,6
insert into employee select 11,6

go

So, when i pass the empid 3, it should return all the children under 3; i.e.
4,5,6,7,8,9,10 and 11

Can i do it in a single query in SQL 2K, without using recursive functions


Thanking in advance
regards
Lara