SQL Server Performance Forum – Threads Archive

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

---

]]>