Find all parents of a child using CTE

; with  CTE as
        (
        SELECT ID,
        parentid,
  1 as level
  FROM Hierarchy
         where ID=125
        union all
        select  child.id
        ,       child.parentid
        ,       level + 1
        from    Hierarchy child
        join    CTE parent
        on      child.id = parent.parentid
        )
select  *
from    CTE

http://www.codeproject.com/Articles/818694/SQL-queries-to-manage-hierarchical-or-parent-child

SQL queries to manage hierarchical or parent-child relational rows in SQL Server

DiponRoy, 15 Sep 2014 CPOL 49.6K 550 26

4.83 (18 votes)

Rate: vote 1vote 2vote 3vote 4vote 5

Some queries to find generation of each row, all possible children’s, all possible parents, managing recursion

Is your email address OK? You are signed up for our newsletters but your email address is either unconfirmed, or has not been reconfirmed in a long time. Please click here to have a confirmation email sent so we can confirm your email address and start sending you newsletters again. Alternatively, you can update your subscriptions.

• Download ParentChildHierarchy_15_Sep_2014_V2.zip - 35.7 KB

Introduction

Here in this post we will try to manage data’s with hierarchical relation or parent-child relation of a specific table in SQL server. Our special concentration would be over,

• Show Generations of each row
• Find all possible parents of a specific row
• Find all possible child’s of a specific row
• Show all possible parents at a column with a separator
• Show all possible child’s at a column with a separator

Background

Let’s pretend

• A row can have only one parent or Null as no parent
• There is at least a row, without parent (parentId is null)
• And at least a row, without any child

Here is our table schema.

Hide   Copy Code

/*drop the tbl*/
--DROP TABLE UserType

/*create the tbl*/
CREATE TABLE UserType(
    Id BIGINT NOT NULL,
    Name VARCHAR(100) NOT NULL,
    ParentId BIGINT NULL    
)

Let’s populate the table with data’s

Hide   Copy Code

/*insert data into table*/
DECLARE @maxCount BIGINT,
        @count BIGINT,
        @parentId BIGINT;        
SET @maxCount = 10;        /*change to input more*/
SET @count = 1;

WHILE @count <= @maxCount
BEGIN
    If @count = 1
        SET @parentId = NULL;
    ELSE
        SET @parentId = @count - 1;
        
    INSERT INTO UserType(Id, Name, ParentId)
        VALUES (@count, 'User_' + CONVERT(VARCHAR(400), @count), @parentId)
    SET @count = @count + 1;
END

So our populated table would be like

Hide   Copy Code

/*show inserted datas*/
SELECT *
    FROM UserType;

Check in live http://www.sqlfiddle.com/#!3/f50a6/1
Now how to find all this generations, parents or child’s using SQL for a specific row …!!!
The answer is using recursion. But to use this recursion we need some Thing Called CTE (Common Table Expressions) or in syntax “WITH” in SQL. If we don’t have any idea about it, we can start with the links or Google for few moments.
http://msdn.microsoft.com/en-us/library/ms175972.aspx
http://technet.microsoft.com/en-us/library/ms190766%28v=sql.105%29.aspx
http://www.codeproject.com/Articles/265371/Common-Table-Expressions-CTE-in-SQL-SERVER
So let’s start with pretty basics.

Regular Join

Joining table with itself based on condition, where ones parentId is equal to another’s Id

Hide   Copy Code

/*regular join to get detail*/    
SELECT ChildUserType.Id, ChildUserType.Name, ParentUserType.Id, ParentUserType.Name
    FROM UserType AS ChildUserType
    LEFT JOIN UserType AS ParentUserType ON ChildUserType.ParentId = ParentUserType.Id;

Check in live http://www.sqlfiddle.com/#!3/f50a6/2

Row Generation

The procedure is something like

• All rows with no parent (NULL), assign generation 0 to them
• Find rows where parent belongs to the generation 0, and assign increased generation to itself
• Do until the recursion is finished.

Hide   Copy Code

/*row generations*/
WITH Hierarchy(ChildId, ChildName, Generation, ParentId)
AS
(
    SELECT Id, Name, 0, ParentId
        FROM UserType AS FirtGeneration
        WHERE ParentId IS NULL        
    UNION ALL
    SELECT NextGeneration.Id, NextGeneration.Name, Parent.Generation + 1, Parent.ChildId
        FROM UserType AS NextGeneration
        INNER JOIN Hierarchy AS Parent ON NextGeneration.ParentId = Parent.ChildId    
)
SELECT *
    FROM Hierarchy
    OPTION(MAXRECURSION 32767)

Check in live http://www.sqlfiddle.com/#!3/f50a6/3

All Possible Parents

check http://stackoverflow.com/a/21233413/2948523
Here we are trying to find all possible parents of a row where it's Id = 5

• Starts with selecting the row where Id = 5
• Find other rows where its id is equal to previously selected’s ParentId
• And continue reduction

Hide   Copy Code

--all posible parents of @id
DECLARE @id BIGINT;
SET @id = 5;
WITH tblParent AS
(
    SELECT *
        FROM UserType WHERE Id = @id
    UNION ALL
    SELECT UserType.*
        FROM UserType  JOIN tblParent  ON UserType.Id = tblParent.ParentId
)
SELECT * FROM  tblParent
    WHERE Id <> @id
OPTION(MAXRECURSION 32767)

Check in live http://www.sqlfiddle.com/#!3/f50a6/5

All Possible Childs

check http://stackoverflow.com/a/21233413/2948523
Here we are trying to find all possible child’s of a row where it’s Id = 5

• Starts with selecting the row where Id = 5
• Find other rows where its ParentId is equal to previously selected’s Id
• And continue reduction

Hide   Copy Code

-- all posible childs of @userTypeId
DECLARE @userTypeId BIGINT;
SET @userTypeId = 5;
WITH tblChild AS
(
    SELECT *
        FROM UserType WHERE ParentId = @userTypeId
    UNION ALL
    SELECT UserType.* FROM UserType  JOIN tblChild  ON UserType.ParentId = tblChild.Id
)
SELECT *
    FROM tblChild
OPTION(MAXRECURSION 32767)

Check in live http://www.sqlfiddle.com/#!3/f50a6/6

All Possible Parents In A Column

Here we are showing all the possible parents Ids at the column with a specific separator ‘.’

Hide   Copy Code

/*row posible parents in a column*/
WITH Hierarchy(ChildId, ChildName, ParentId, Parents)
AS
(
    SELECT Id, Name, ParentId, CAST('' AS VARCHAR(MAX))
        FROM UserType AS FirtGeneration
        WHERE ParentId IS NULL    
    UNION ALL
    SELECT NextGeneration.Id, NextGeneration.Name, Parent.ChildId,
    CAST(CASE WHEN Parent.Parents = ''
        THEN(CAST(NextGeneration.ParentId AS VARCHAR(MAX)))
        ELSE(Parent.Parents + '.' + CAST(NextGeneration.ParentId AS VARCHAR(MAX)))
    END AS VARCHAR(MAX))
        FROM UserType AS NextGeneration
        INNER JOIN Hierarchy AS Parent ON NextGeneration.ParentId = Parent.ChildId    
)
SELECT *
    FROM Hierarchy
OPTION(MAXRECURSION 32767)

Check in live http://www.sqlfiddle.com/#!3/f50a6/7

All Possible Childs In A Column

Here we are showing all the possible child’s Ids at the column with a specific separator ‘.’

Hide   Copy Code

/*row posible childs in a column*/
WITH Hierarchy(ChildId, ChildName, ParentId, Childs)
AS
(
    SELECT Id, Name, ParentId, CAST('' AS VARCHAR(MAX))
        FROM UserType AS LastGeneration
        WHERE Id NOT IN (SELECT COALESCE(ParentId, 0) FROM UserType)     
    UNION ALL
    SELECT PrevGeneration.Id, PrevGeneration.Name, PrevGeneration.ParentId,
    CAST(CASE WHEN Child.Childs = ''
        THEN(CAST(Child.ChildId AS VARCHAR(MAX)))
        ELSE(Child.Childs + '.' + CAST(Child.ChildId AS VARCHAR(MAX)))
    END AS VARCHAR(MAX))
        FROM UserType AS PrevGeneration
        INNER JOIN Hierarchy AS Child ON PrevGeneration.Id = Child.ParentId    
)
SELECT *
    FROM Hierarchy
OPTION(MAXRECURSION 32767)