SQL Server Performance

SQL Recursive Qry doubt

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

  1. ssathish New Member

    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
  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
    MS, MCDBA, OCA, CIW

  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,
    Sathish.S

Share This Page