Get all child records of a record from a self referencing table

Hi Experts,

          I have a self referencing table (each record may or may not have a reference to another record within the same table) and I want to be able to get all child records for a particular record!  I am looking for a script for the best way to accomplish this!


ID     ParentID     Name
1       NULL           U.S.A.
2       1                   PA
3       2                   Pittsburgh
4       2                   Harrisburg

So if I would pass the stored procedure the ID=1, it would retrun PA, Pittsburgh, and Harrisburg, while if I pass it the ID=2, it would only retrun Pittsburgh, and Harrisburg

Any help would be greatly appreciated!

Who is Participating?
sognoctConnect With a Mentor Commented:
create procedure getAllChilds (@val as int)
        DECLARE @TempTable table (id int, ParentID int, Name nvarchar(80))
        insert into  @TempTable (id, ParentID, Name )
                  select id, ParentID, Name  
                        from mytable
                        Where ParentID = @val
      WHILE (SELECT count(0) from @TempTable t1
                        INNER JOIN  mytable t2 ON = t2.parentid
                        LEFT JOIN @TempTable t3 ON =
                        WHERE IS NULL )>0
            insert into  @TempTable (id, ParentID, Name )
                  select, t2.ParentID, t2.Name
                  from @TempTable t1
                  INNER JOIN  mytable t2 ON = t2.parentid
                  LEFT JOIN @TempTable t3 ON =
                  WHERE IS NULL
        select * FROM @TempTable
axnst2Author Commented:
Oh, I forgot to mention that we have no idea "how deep the rabbit hole goes"!  So the number of tiers is n!
Steve WalesSenior Database AdministratorCommented:
Sounds like what you need is a recursive query with a common table expression (CTE).

I don't claim to be an expert on CTE's and their syntax but there's a detailed example in the Microsoft documentation on doing pretty much want you want, but with an employee hierarchy, instead of a geographical one.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

for a dirty fast solution
just something like

WHILE (SELECT count(0) from mytable where parentid=@val)>0
      select @val = min(id) from mytable where parentid = @val
      select * from mytable Where parentid=@val;
Saurabh BhadauriaCommented:
You need a recursive CTE... here is a example code....
Here is a example code..

First create Sp...
create procedure your_proc (@id int)
;with cte_h
    (   select,id  from #tt t1
              where t1.ParentID=@id
         union all 
         select,  from #tt  a join cte_h b on
select name from cte_h option (maxrecursion 1000)



Open in new window

Then exec below code....
create table #tt (ID int,     ParentID int     ,Name varchar(20))
insert into #tt
select 1,       NULL   ,        'U.S.A.'
union all select 2       ,1                   ,'PA'
union all select 3       ,2                   ,'Pittsburgh'
union all select  4,       2                   ,'Harrisburg'

select * from #tt

exec your_proc 1

Open in new window

axnst2Author Commented:
Worked like a charm Sir/Ma'am!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.