SQL Server Performance

SQL Recursive Qry doubt

Discussion in 'General DBA Questions' started by ssathish, Jan 23, 2003.

  1. ssathish New Member

    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

    as my result set. Is there any way to do in Single query
    or any other alternative....
    kindly help me,
    Thank you,

    With regards,
  2. tkelley New Member

    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

  3. arb New Member

    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
  4. ssathish New Member

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

    With regards,

Share This Page