Parent and Child Relationship in the Same table - SQL Server

Parent and Child Relationship

In this article, I'll explain the parent and child relationship in the same table.

Let's take the example by declaring the SQL table and insert some records.

Step 1: Declare the table and add some dummy data

DECLARE @table TABLE
(
Id INT,
Name varchar(50),
ManagerId INT

)
INSERT INTO @table VALUES(1,'Sunny',2)
INSERT INTO @table VALUES(2,'Nitin',4)
INSERT INTO @table VALUES(3,'Amit',5)
INSERT INTO @table VALUES(4,'Sumit',1)
INSERT INTO @table VALUES(5,'Jay',null)

SELECT * FROM @table

When you run the query and you will find below result

In the above snapshot, User belongs to the following manager. We need output as below.

Sunny - Nitin
Nitin - Sumit
Amit - Jay
Sumit - Sunny
Jay - No Manager Assigned


Step 2: Write a query to achieve the desired result.

1. With Help of Join

SELECT a.Name,b.Name as ManagerName
FROM   @table a left join @table b on
 b.Id = a.ManagerId

2. With Help of Sub Query

SELECT a.Name, (SELECT b.Name
                       FROM @table b
                       WHERE b.Id=a.ManagerId)as ManagerName
FROM   @table a

Step 3: Output would be the same for the above 2 queries.




This is all about the article. I hope you like it.