Find hierarchical data in SQL server (Recursion)

Introduction:

Here I will show you how to find the hierarchical data of a sql server, like we did in Team management process. In a team process we have different users having their own parent. So if we want to go from level 1 to level n then what should we have to do? Lets come and check how to traverse all the users at all level from top to bottom and bottom to top.

Description:

Previously we have discussed about Generate Genealogy view in ASP.NET C# using Google Organizational Chart, here we have discussed about the hierarchical data. In this post we have just shown the data, but here we will use recursion to get the data. Let’s see how to do?

First create a table structure of our sql table.

Column	Datatype
UserId (Primary Key)	Int (Identity(1,1))
Email	NVarChar(255)
Name	NVarChar(255)
Password	NVarChar(255)
ParentId	Int
IsDelete	Int

Notice that we have put ParentId in the same table so that we don't need any table to follow. This will lead to best practice of self-joining.

Now let’s see how to fetch the data using self-joining.

Find all the user with their parent information.

select a.UserId, a.Name, a.Email, b.UserId, b.Name, b.Email from tblUser a inner join tblUser b on a.ParentId = b.UserID

To find the information about a particular user or a particular combination follow this query.

select a.UserId, a.Name, a.Email, b.UserId, b.Name, b.Email from tblUser a inner join tblUser b on a.ParentId = b.UserID and a.UserId = '<user_id>'

Here we have took a, b two instance of tblUser and join them with the combination to get the info about the user as well as parents.

You have got individual information now it’s time to get the all traversing data of the whole table. For that follow the following query.

Query to find all possible parents

DECLARE @UserId INT; SET @UserId = 4; WITH tblParent AS ( SELECT * FROM tblUser WHERE UserId = @UserId UNION ALL SELECT tblUser.* FROM tblUser  JOIN tblParent  ON tblUser.UserId = tblParent.ParentId ) SELECT * FROM  tblParent WHERE UserId <> @UserId OPTION(MAXRECURSION 32767)

Query to find all possible children

DECLARE @userId INT; SET @userId = 1; WITH tblChild AS ( SELECT * FROM tblUser WHERE ParentId = @userId UNION ALL SELECT tblUser.* FROM tblUser  JOIN tblChild  ON tblUser.ParentId = tblChild.UserId ) SELECT * FROM tblChild OPTION(MAXRECURSION 32767)

Run the query and check the result. It will return the all possible list of all parent (bottom to top) and child (top to bottom) user's info. Check it with your project data...

CodeProject

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email