Introduction
Here, in this post, we will try to manage data 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 childs 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:
CREATE TABLE UserType(
Id BIGINT NOT NULL,
Name VARCHAR(100) NOT NULL,
ParentId BIGINT NULL
)
Let’s populate the table with data:
DECLARE @maxCount BIGINT,
@count BIGINT,
@parentId BIGINT;
SET @maxCount = 10;
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:
SELECT *
FROM UserType;
Check in live http://www.sqlfiddle.com/#!3/f50a6/1
Now how to find all these generations, parents or childs using SQL for a specific row …!!!
The answer is using recursion. But to use this recursion, we need something 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.
So let’s start with pretty basic stuff.
Regular Join
Joining table with itself based on condition, where ones parentId is equal to another’s Id.
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
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 its Id = 5.
- Starts with selecting the row where
Id = 5 - Find other rows where its
id is equal to previously selected ParentId - And continue reduction
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 childs of a row where its Id = 5.
- Starts with selecting the row where
Id = 5 - Find other rows where its
ParentId is equal to previously selected Id - And continue reduction
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 parent Ids at the column with a specific separator ‘.’
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 ‘.’
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)
Check in live http://www.sqlfiddle.com/#!3/f50a6/8
Recursion Limit !!!
In all of the previous queries, we have used syntax like:
OPTION(MAXRECURSION 32767)
This specifies the maximum number of recursions in CTE. Now if we don’t use this OPTION(MAXRECURSION 32767), by default, it is 100.
We need to specify this number depending on the recursion requirement.
If More Recursion Needed !!!
With MAXRECURSION value equal to 0 means that no limit is applied to the recursion level, but remember a recursion should end at some level.
OPTION(MAXRECURSION 0)
To increase the recursion number, excluding CTE’s maximum Limit. We can follow some instruction like the links, or Google for some time.
Limitations
Yes, there could be something which I misunderstood or presented. So if you find anything, just let me know.
Find the SQL file as attachment.
