SQL: Find Employee Who are Managers Using Self Join

Find Employee Who are Managers Using Self Join

Today We will learn about “How to Find Employee Who are Managers Using Self Join” in SQL server. Self Joins are easy to understand and it is one of the most common sql server interview question to “Find employees who are managers”. So lets understand the self join.

 
Self-Join as it’s name tell us ‘A Join with itself’. Self-Join us a query in which a table is joined to itself. Self-join are used to compare values in a column with other values in the same column in the same table. One practical use for self-joins is to obtain running counts and running totals in an SQL Query.

 

CREATING A NEW TABLE EMPLOYEE.

CREATE TABLE Employees (
EmpId INT NOT NULL PRIMARY KEY,
EmpName VARCHAR(50) DEFAULT NULL,
ManagerId INT DEFAULT NULL
)

INSERTING DATA INTO EMPLOYEE TABLE.

INSERT INTO employees (EmpId,EmpName,ManagerId) VALUES (1,'Deepak','2');
INSERT INTO employees (EmpId,EmpName,ManagerId) VALUES (2,'Annu',NULL);
INSERT INTO employees (EmpId,EmpName,ManagerId) VALUES (3,'Jai','2');
INSERT INTO employees (EmpId,EmpName,ManagerId) VALUES (4,'Jitendra','1');
INSERT INTO employees (EmpId,EmpName,ManagerId) VALUES (5,'Vaishali','1');
INSERT INTO employees (EmpId,EmpName,ManagerId) VALUES (6,'Philip','4');

SELECT EMPLOYEE WHO ARE MANAGER.

SELECT DISTINCT e.EmpId AS 'ManagerId', e.EmpName AS 'ManagerName'
FROM Employees e, Employees m WHERE e.EmpId = m.ManagerId

1

 

 

 

 

 

 

SELECT ALL MANAGER NAME WITH THERE TOTAL SUBORDINATES EMPLOYEES.

SELECT COUNT(emp.EmpId) AS 'EmployeeCount', mng.ManagerName AS 'ManagerName'
FROM employees emp,
(SELECT DISTINCT e.EmpId AS 'ManagerId', e.EmpName AS 'ManagerName'
FROM employees e, employees m
WHERE e.EmpId = m.ManagerId) mng
WHERE emp.ManagerId = mng.ManagerId
GROUP BY mng.ManagerId, mng.ManagerName

 

2

 

 

 

 

 

 

SELECT ALL MANAGER NAME WHOSE TOTAL SUBORDINATES

EMPLOYEES ARE GREATER THAN 1.

SELECT COUNT(emp.EmpId) AS 'EmployeeCount', mng.ManagerName AS 'mng_name'
FROM employees emp,
(SELECT DISTINCT e.EmpId 'ManagerId', e.EmpName AS 'ManagerName'
FROM employees e, employees m
WHERE e.EmpId = m.ManagerId) mng
WHERE emp.ManagerId = mng.ManagerId
GROUP BY mng.ManagerId,mng.ManagerName HAVING COUNT(emp.EmpId) > 1;

3

 

 

 

 

Hope this will help in understanding self join.

Some useful links:
https://technet.microsoft.com/en-us/library/ms177490(v=sql.105).aspx

Deepak Arora

Deepak Arora is a Software Developer and working continuously in this field from 2011. He work mainly with Microsoft Technology. He holds a Master's in Computer Application and he is MCTS certified person.

Leave a Reply

Your email address will not be published. Required fields are marked *