Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

EmpID EmpName ReportsTo
1 Jacob NULL
2 Rui NULL
3 Jacobson NULL
4 Jess 1
5 Steve 1
6 Bob 1
7 Smith 2
8 Bobbey 2
9 Steffi 3
10 Bracha 3
11 John 5
12 Michael 6
13 Paul 6
14 Lana 7
15 Johnson 7
16 Mic 8
17 Stev 8
18 Paulson 9
19 Jessica 10

Expected Output

Employee Manager ManagersManager
Jacob NULL NULL
Rui NULL NULL
Jacobson NULL NULL
Jess Jacob NULL
Steve Jacob NULL
Bob Jacob NULL
Smith Rui NULL
Bobbey Rui NULL
Steffi Jacobson NULL
Bracha Jacobson NULL
John Steve Jacob
Michael Bob Jacob
Paul Bob Jacob
Lana Smith Rui
Johnson Smith Rui
Mic Bobbey Rui
Stev Bobbey Rui
Paulson Steffi Jacobson
Jessica Bracha Jacobson

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

Advertisements