SQL Puzzle | Manager & Manager’s Manager Puzzle

You have to write a query that shows employee name, manager and the managers manager name. The catch is that you can only use Single SELECT statement only
to get the results

For details please check out the sample input and the expected output below-

Sample Inputs

Expected Output

Rules/Restrictions

• The challenge is to do it with T-SQL statements only.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data

--


--Create Table
 
CREATE TABLE Employees  (EmpID INT, EmpName VARCHAR(20), ReportsTo INT)
 
--Insert Data
INSERT INTO Employees(EmpID, EmpName, ReportsTo)
SELECT 1, 'Jacob', NULL UNION ALL
SELECT 2, 'Rui', NULL UNION ALL
SELECT 3, 'Jacobson', NULL UNION ALL
SELECT 4, 'Jess', 1 UNION ALL
SELECT 5, 'Steve', 1 UNION ALL
SELECT 6, 'Bob', 1 UNION ALL
SELECT 7, 'Smith', 2 UNION ALL
SELECT 8, 'Bobbey', 2 UNION ALL
SELECT 9, 'Steffi', 3 UNION ALL
SELECT 10, 'Bracha', 3 UNION ALL
SELECT 11, 'John', 5 UNION ALL
SELECT 12, 'Michael', 6 UNION ALL
SELECT 13, 'Paul', 6 UNION ALL
SELECT 14, 'Lana', 7 UNION ALL
SELECT 15, 'Johnson', 7 UNION ALL
SELECT 16, 'Mic', 8 UNION ALL
SELECT 17, 'Stev', 8 UNION ALL
SELECT 18, 'Paulson', 9 UNION ALL
SELECT 19, 'Jessica', 10
 
--Verify Data
SELECT * FROM Employees E

--

—

Solution 1

--

SELECT E.EmpName, r.EmpName ManagerName, s.EmpName ManagersManager
FROM Employees E
OUTER APPLY
(
       SELECT TOP 1 m1.EmpName , m1.ReportsTo FROM Employees m1 WHERE m1.EmpId= E.ReportsTo
)r
OUTER APPLY
(
       SELECT TOP 1 m2.EmpName FROM Employees m2 WHERE m2.EmpId= r.ReportsTo
)s


--

Solution 2

--

SELECT  E1.EmpName "Employee", E2.EmpName "Manager", E3.EmpName "Managers Manager"
FROM Employees E1 LEFT JOIN Employees E2 on E1.ReportsTo = E2.EmpId 
LEFT JOIN Employees E3 on E2.ReportsTo = E3.EmpId


--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com