Recursively grab all data based on a parent id

Question

We have a table where rows recursively link to another row. I want to pull data associated with a given parentId and all it's children. Where parentId is one from the root row.

I thought I have seen or done something like that before, but I am unable to find it now. Can this be done in SQL or is it better to do this in code?

I want the list to look like this when I'm done:

• Parent
  • Child
  • • Grandchild

Answer 1

This can be done in SQL Server 2005 and above using Common Table Expressions (CTEs). Here is a great link from MSDN describing recursive queries: Recursive Queries Using Common Table Expressions

Here is an example:

If you imagine a hierarchical line of people, this query will let you see the complete line of any person AND calculates their place in the hierarchy. It can be modified to find any child relationship.

Instead of the ID of the person, you swap in the ID of the row you are using as your parent.

--Create table of dummy data
create table #person (
personID integer IDENTITY(1,1) NOT NULL,
name      varchar(255) not null,
dob       date,
father    integer
);

INSERT INTO #person(name,dob,father)Values('Pops','1900/1/1',NULL);  
INSERT INTO #person(name,dob,father)Values('Grandma','1903/2/4',null);
INSERT INTO #person(name,dob,father)Values('Dad','1925/4/2',1);
INSERT INTO #person(name,dob,father)Values('Uncle Kev','1927/3/3',1);
INSERT INTO #person(name,dob,father)Values('Cuz Dave','1953/7/8',4);
INSERT INTO #person(name,dob,father)Values('Billy','1954/8/1',3);

DECLARE @OldestPerson INT; 
SET @OldestPerson = 1; -- Set this value to the ID of the oldest person in the family

WITH PersonHierarchy (personID,Name,dob,father, HierarchyLevel) AS
(
   SELECT
      personID
      ,Name
      ,dob
      ,father,
      1 as HierarchyLevel
   FROM #person
   WHERE personID = @OldestPerson

   UNION ALL

   SELECT
    e.personID,
      e.Name,
      e.dob,
      e.father,
      eh.HierarchyLevel + 1 AS HierarchyLevel
   FROM #person e
      INNER JOIN PersonHierarchy eh ON
         e.father = eh.personID
)

SELECT *
FROM PersonHierarchy
ORDER BY HierarchyLevel, father;

DROP TABLE #person;