Query help

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
Thanking in advance
regards
Laraselect * from employee as e
where e.parentid=<managerID>
"Lara" <lara169@.gmail.com> wrote in message
news:OTS6tH18FHA.4076@.tk2msftngp13.phx.gbl...
> 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
>
> Thanking in advance
> regards
> Lara
>|||Thanks martin,
But this is not the one i needed. i need all the children under this
parentid
"Martin" <x@.y.z> wrote in message
news:%23tqUlX18FHA.636@.TK2MSFTNGP10.phx.gbl...
> select * from employee as e
> where e.parentid=<managerID>
> "Lara" <lara169@.gmail.com> wrote in message
> news:OTS6tH18FHA.4076@.tk2msftngp13.phx.gbl...
>|||Do you mean recursively?
"Lara" <lara169@.gmail.com> wrote in message
news:exAsfZ18FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Thanks martin,
> But this is not the one i needed. i need all the children under this
> parentid
>
> "Martin" <x@.y.z> wrote in message
> news:%23tqUlX18FHA.636@.TK2MSFTNGP10.phx.gbl...
>|||Select
c.*
from employee as m
left join employee as c
on c.ParentID = m.EmpID
"Lara" <lara169@.gmail.com>, haber iletisinde unlar
yazd:OTS6tH18FHA.4076@.tk2msftngp13.phx.gbl...
> 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
>
> Thanking in advance
> regards
> Lara
>|||Something like this (I've not tested it - there are some syntax errors)
declare @.tab table (empid int ,parentid int)
declare @.rowsaffected int
set @.rowsaffected=-1
declare @.empid int
set @.empid=3
insert into @.tab (select * from employee where empid=@.empid)
set @.rowsaffected=@.@.rowcount
while @.rowsaffected<>0 -- stop when there are no more employees added to
table
begin
insert into @.tab (select * from employee
where
parentid in (select * empid from @.tab) and -- select next level down
from those already selected
empid not in (select empid from @.tab) -- don't double select
)
set @.rowsaffected=@.@.rowcount
end
select empid from @.tab where empid<>@.empid -- don't return orginal employee
id
"Martin" <x@.y.z> wrote in message
news:Oi$Jzb18FHA.808@.TK2MSFTNGP09.phx.gbl...
> Do you mean recursively?
> "Lara" <lara169@.gmail.com> wrote in message
> news:exAsfZ18FHA.2364@.TK2MSFTNGP12.phx.gbl...
>|||Here's the correct syntax
declare @.tab table (empid int ,parentid int)
declare @.rowsaffected int
set @.rowsaffected=-1
declare @.empid int
set @.empid=3
insert into @.tab(empid,parentid) (select empid,parentid from employee where
empid=@.empid)
set @.rowsaffected=@.@.rowcount
while @.rowsaffected<>0
begin
insert into @.tab(empid,parentid) (select empid,parentid from employee
where
parentid in (select empid from @.tab) and -- select next level down from
those already selected
empid not in (select empid from @.tab) -- don't double select
)
set @.rowsaffected=@.@.rowcount
end
select empid from @.tab where empid<>@.empid -- don't return orginal employee
id
"Martin" <x@.y.z> wrote in message
news:eA1O6n18FHA.3984@.TK2MSFTNGP11.phx.gbl...
> Something like this (I've not tested it - there are some syntax errors)
> declare @.tab table (empid int ,parentid int)
> declare @.rowsaffected int
> set @.rowsaffected=-1
> declare @.empid int
> set @.empid=3
> insert into @.tab (select * from employee where empid=@.empid)
> set @.rowsaffected=@.@.rowcount
> while @.rowsaffected<>0 -- stop when there are no more employees added to
> table
> begin
> insert into @.tab (select * from employee
> where
> parentid in (select * empid from @.tab) and -- select next level down
> from those already selected
> empid not in (select empid from @.tab) -- don't double select
> )
> set @.rowsaffected=@.@.rowcount
> end
> select empid from @.tab where empid<>@.empid -- don't return orginal
> employee id
>
> "Martin" <x@.y.z> wrote in message
> news:Oi$Jzb18FHA.808@.TK2MSFTNGP09.phx.gbl...
>|||Have a look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
When I say 'look' I mean copy/paste/test.
ML|||Lara (lara169@.gmail.com) writes:
> 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
Here is how you can do this on SQL 2005:
with emp (empid, parentID) as
(select empid, parentID = NULL
from employee
where empid = 3
union all
select e.empid, e.parentID
from emp
join employee e on e.parentID = emp.empid)
select empid from emp where empid <> 3
I reckon that you are likely to still use SQL 2000, but I wanted to show
that this is a lot easier on SQL 2005.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Martin, But can we do it in a single query ?
"Martin" <x@.y.z> wrote in message
news:%23loxEq18FHA.4084@.TK2MSFTNGP10.phx.gbl...
> Here's the correct syntax
>
> declare @.tab table (empid int ,parentid int)
> declare @.rowsaffected int
> set @.rowsaffected=-1
> declare @.empid int
> set @.empid=3
> insert into @.tab(empid,parentid) (select empid,parentid from employee
> where empid=@.empid)
> set @.rowsaffected=@.@.rowcount
> while @.rowsaffected<>0
> begin
> insert into @.tab(empid,parentid) (select empid,parentid from employee
> where
> parentid in (select empid from @.tab) and -- select next level down from
> those already selected
> empid not in (select empid from @.tab) -- don't double select
> )
> set @.rowsaffected=@.@.rowcount
> end
> select empid from @.tab where empid<>@.empid -- don't return orginal
> employee id
> "Martin" <x@.y.z> wrote in message
> news:eA1O6n18FHA.3984@.TK2MSFTNGP11.phx.gbl...
>sql