How do i do an inner/ recurrsive join on a single table?

How do I join a table on its self i.e. lets say for example that I have a table with the following schema i.e.

Table 1, with columns ID, Name, ParentId and lets say ParentId was a forgeign key for a matching row in the same table, how would I get all rows returned with all rows containing parented in order so e.g.

1, aaa, null
2, bbb,1
3,ccc,2
4,ddd,null
5,eee,4

And lets say i made a query which said i want all rows with an id of 1 and all child rows from 1 whether there child or child of child,or child of child of child and so on,  .

So e.g. lets say i wanted all row related to id of . So in this instance i'd get the following returned in this order i.e.

1, aaa, null  ---> Parent
     2, bbb,1 ----> Child
         3,ccc,2 ----> Child of child with parent od 1
           

Thanks
Webbo
Webbo_1980Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Om PrakashCommented:
Please check the following tutorial on recursive queries:
http://www.vbforums.com/showthread.php?t=366078

This article contains same information that you need
0
Paul_Harris_FusionCommented:
The following function is adapted from Example C at
http://msdn.microsoft.com/en-us/library/ms186755.aspx

CREATE FUNCTION dbo.FindChildren (@Id INTEGER)
RETURNS @results TABLE
(
    Id int primary key NOT NULL,
    ParentId int ,
    HierarchyLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
   WITH resultsTable ( Id, ParentId, HierarchyLevel) AS
      (SELECT Id, ParentId, 1 from temp t WHERE t.Id = @Id
     UNION ALL
       SELECT t.Id, t.ParentId, r.HierarchyLevel + 1
     from temp t inner Join resultsTable r on t.ParentId = r.Id
    )

-- copy the required columns to the result of the function
   INSERT @results
   SELECT Id, ParentId, HierarchyLevel
   FROM resultsTable
   RETURN
END;


My test table is called temp so you would need to change that for your own schema and column names

To return the results...

Select * from FindChildren(1)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.