Problem

Parent-Child is one of popular relations when you design a relational database model. Obviously, you need also to retrieve data that will show their parent records from the relationship.

Solution

The most simplest way is to use Common Table Expressions (CTE) and run recursive process to identify parent records. CTE provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which initially CTE is repeatedly executed to return subsets of data until the complete result set is obtained.

To understand how to work with a recursive CTE, I used Employee model to demonstrate. Displaying employees in organization chart is popular example and easy to understand.

1-Create data

USE tempdb;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ‘Department’)
DROP TABLE dbo.Department;
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ‘Employee’)
DROP TABLE dbo.Employee;
GO
CREATE TABLE dbo.Department (DepartmentID SMALLINT, DepartmentName NVARCHAR(50)) ;
GO
CREATE TABLE dbo.Employee
(
EmployeeID SMALLINT NOT NULL,
FirstName NVARCHAR(30) NOT NULL,
LastName NVARCHAR(40) NOT NULL,
Title NVARCHAR(50) NOT NULL,
DepartmentID SMALLINT NOT NULL,
ManagerID SMALLINT NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);
GO
INSERT INTO Department
VALUES (1,’Executive’),(2,’Marketing’),(3,’Human Resources’),(4,’Sales’)
GO
INSERT INTO Employee
VALUES(1, N’Ken’, N’Sánchez’, N’Chief Executive Officer’,1,NULL)
,(2, N’Brian’, N’Welcker’, N’Vice President of Sales’,4,1)
,(3, N’Stephen’, N’Jiang’, N’North American Sales Manager’,4,2)
,(4, N’Michael’, N’Blythe’, N’Sales Representative’,4,3)
,(5, N’Linda’, N’Mitchell’, N’Sales Representative’,4,3)
,(6, N’Syed’, N’Abbas’, N’Pacific Sales Manager’,4,2)
,(7, N’Lynn’, N’Tsoflias’, N’Sales Representative’,4,6)
,(8, N’David’,N’Bradley’, N’Marketing Manager’, 2, 2)
,(9, N’Mary’, N’Gibson’, N’Marketing Specialist’, 2, 8)
,(10, N’Paula’, N’Barreto de Mattos’, N’Human Resources Manager’, 3, 1)
,(11, N’Vidur’, N’Luthra’, N’Recruiter’, 3, 10)
,(12, N’Joe’, N’Piere’, N’Sales’, 4, 4);
GO

2- Retrieve data

There are two steps at this step. We gathered all employees without their manager first (ManagerID). Then we joined this subset of data to other subsets to identify their children.

WITH EmployeeCTE (ManagerID, EmployeeID, FirstName, LastName, Title, DepartmentID, Level)
AS
(
–Step 1: Find all anchors — It means ManagerID is null
SELECT e.ManagerID, e.EmployeeID,e.FirstName, e.LastName, e.Title, e.DepartmentID, 0 AS Level
FROM Employee e
WHERE e.ManagerID IS NULL

–Step 2: Run recursive to find members
UNION ALL
SELECT e.ManagerID, e.EmployeeID,e.FirstName, e.LastName, e.Title, e.DepartmentID, Level+1 AS Level
FROM Employee e
INNER JOIN EmployeeCTE cte ON e.ManagerID = cte.EmployeeID
)
SELECT ManagerID, EmployeeID, FirstName, LastName, Title, d.DepartmentName, Level
FROM EmployeeCTE e
INNER JOIN Department d ON e.DepartmentID = d.DepartmentID

Observed the result,  Kan Sanchez manages Brian Welcker whom is Vice President Of Sales, and Paula Barreto de Mattos whom is Human Resources Manager..

Next, Brian Welcker manages Stephen Jiang, Syed Abbas, David Bradley and going on..

Recursive_Employee

Conclusion

CTE is the great way to help us if we want to run recursive algorithm on a dataset in SQL Server. Obviously, you will have another approach when you work with other language programming but CTE provides the significant advantage of being able to work on multiple rows.

 

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s