SQL Recursive Qry doubt

Hai,
I have table called tblClassification with Id,ParentId and Description as
fields. I need to fetch all the child classifications that fall under given
classifiaction.(not only immediate child,also all level child)
say i have classification called 'Entertainment', under which i have 'Movie'
and 'Sports'. Under Sports i have 'Baseball' and 'Basketball'.
Under Moive i have 'Tragedy','Comedy' and 'Thriller'.

if i give
'Entertainment's classification Id ,I should get

Moive,Sport,Baseball,Basketball,Tragedy,Comedy,Thriller.
as my result set. Is there any way to do in Single query
or any other alternative....
kindly help me,
Thank you,


With regards,
Sathish.S

Yes, do a join with ParentID as the key and then group by ParentID. Your question was a bit confusing, though. Is all of this in one table, or are the child info in a seperate table?

----------
T Kelley
MS, MCDBA, OCA, CIW

You will need to write a stored procedure to do this.

Something along the lines of (this is off the top of my head, may not be 100% - don't have Query Analyzer running here to test this):



create procedure spFindChildren (@ClassID int) as

create table #tmp (id int)

insert into #tmp (id) values (@ClassID)

while @@rowcount > 0
insert into #tmp (id)
select c.id
from tblClassification as c
inner join #tmp t on c.parentid = t.id
where not exists (select * from #tmp where id = c.id)

select * from #tmp

Great, it works fine....thank u arb



With regards,
Sathish.S