Hi All,
I know there can be lot better ways to do the same thing
(Finding all the childs of a given parent) . Please Provide some
insight to improve my understanding of the topic .Trees and
Hierarchies.
Create Table ParentChild
(
Pid varchar(20),
Child varchar(20)
)
Insert into ParentChild values('Jatinder','k')
Insert into ParentChild values('Jatinder','B')
Insert into ParentChild values('Jatinder','C')
Insert into ParentChild values('Jatinder','D')
Insert into ParentChild values('Jatinder','E')
Insert into ParentChild values('Jatinder','F')
Insert into ParentChild values('Jatinder','G')
Insert into ParentChild values('B','aa')
Insert into ParentChild values('C','dd')
Insert into ParentChild values('D','ee')
Insert into ParentChild values('E','ff')
Insert into ParentChild values('F','gg')
Insert into ParentChild values('G',null)
Insert into ParentChild values('aa','xx')
Declare @.cnt int
Declare @.done int
Declare @.Lev int
Set @.Lev = 1
Create Table AllChilds ( Child varchar(20) unique,Lev int)
Insert Into AllChilds Select Child , @.Lev From ParentChild Where PID =
'jatinder'
Set @.cnt = 1
While @.@.rowcount > 0
Begin
Insert Into AllChilds
Select P.Child , (@.Lev + 1) From ParentChild P , AllChilds A
Where P.PID = A.Child And Lev = @.Lev And P.Child is Not Null
If @.@.RowCount = 0
Break
Set @.Lev = @.Lev + 1
End
Select * From AllChilds
Drop Table AllChilds
Drop Table ParentChild
With Warm Regards
Jatinder SinghTake a look at Itzik's example posted sometimes ago
create table users (userID Int, parentID Int)
insert into users select 1, 0 union all select 2, 1
union all select 3, 2 union all select 4, 3 union all
select 5, 4 union all select 6, 4 union all select 7, 5
union all select 8, 6 union all select 9, 7 union all select 10, 8
create function dbo.fn_getpath(@.uid as int) returns varchar(8000)
as
begin
declare @.path as varchar(8000);
set @.path = cast(@.uid as varchar(10));
while @.uid <> 0
begin
set @.uid = (select parentid from users where userid = @.uid);
set @.path = @.path + ',' + cast(@.uid as varchar(10));
end
return @.path;
end
go
select dbo.fn_getpath(5)
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1134572287.593693.239090@.g44g2000cwa.googlegroups.com...
> Hi All,
> I know there can be lot better ways to do the same thing
> (Finding all the childs of a given parent) . Please Provide some
> insight to improve my understanding of the topic .Trees and
> Hierarchies.
> Create Table ParentChild
> (
> Pid varchar(20),
> Child varchar(20)
> )
> Insert into ParentChild values('Jatinder','k')
> Insert into ParentChild values('Jatinder','B')
> Insert into ParentChild values('Jatinder','C')
> Insert into ParentChild values('Jatinder','D')
> Insert into ParentChild values('Jatinder','E')
> Insert into ParentChild values('Jatinder','F')
> Insert into ParentChild values('Jatinder','G')
> Insert into ParentChild values('B','aa')
> Insert into ParentChild values('C','dd')
> Insert into ParentChild values('D','ee')
> Insert into ParentChild values('E','ff')
> Insert into ParentChild values('F','gg')
> Insert into ParentChild values('G',null)
> Insert into ParentChild values('aa','xx')
> Declare @.cnt int
> Declare @.done int
> Declare @.Lev int
> Set @.Lev = 1
> Create Table AllChilds ( Child varchar(20) unique,Lev int)
> Insert Into AllChilds Select Child , @.Lev From ParentChild Where PID =
> 'jatinder'
> Set @.cnt = 1
> While @.@.rowcount > 0
> Begin
> Insert Into AllChilds
> Select P.Child , (@.Lev + 1) From ParentChild P , AllChilds A
> Where P.PID = A.Child And Lev = @.Lev And P.Child is Not Null
> If @.@.RowCount = 0
> Break
> Set @.Lev = @.Lev + 1
> End
> Select * From AllChilds
> Drop Table AllChilds
> Drop Table ParentChild
> With Warm Regards
> Jatinder Singh
>|||"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1134572287.593693.239090@.g44g2000cwa.googlegroups.com...
> Hi All,
> I know there can be lot better ways to do the same thing
> (Finding all the childs of a given parent) . Please Provide some
> insight to improve my understanding of the topic .Trees and
> Hierarchies.
Go get a copy of Celko's book on Trees and hierarchies
ISBN: 1558609202
Excellent stuff in there.
Rick Sawtell
MCT, MCSD, MCDBA|||There's also a typo in one of the functions. Google for it to find Joe
Celko's first-hand errata corrige.
ML
http://milambda.blogspot.com/
