Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Find hierarchical data in SQL server (Recursion)

May 26, 2015
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...

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 RSS subscribe by email Subscribe by Email

3 comments :

Unknown said...

in which case this can be used ? please provide a demo as you always do.

Arkadeep De said...

Hi Nadeem, you can use it in any application where it has any hierarchical data present, like team management where you can have say some managers, and employees under those managers(parent-child relation) or you can use it in Multi-level marketing (MLM) software where they actually form a tree structure to put their customers.

Abdur Razique said...

Hi author, it's really helpful post for me.
But only if the User id is an Integer, plz also explain me what if the User id is a var/varchar/nchar?

Because User id may also contain numeric or alpha numeric values.
Your quicker response will help me a lot...

Regards

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.