CODEWITHSUNDEEP
sqlsql-server
ghost
ghost

Reputation: 13

Can anyone explain how below query is working?

IF (Object_ID('Employee1') is Not Null)
   Drop Table Employee1

Create Table Employee1(
   EID int primary key,
   Name varchar(50),
   MID int references Employee1(EID)
)

Insert into Employee1 values
   (1,'Peter',2),
   (2,'Nancy',3),
   (3,'Bob',null),
   (4,'Philip',5),
   (5,'Jason',3),
   (6,'Gilbert',5)

Get me EID, Name of all employees who are manager of managers

Below is the query I wrote for that question

select distinct 
   e1.eid, 
   e1.name, 
   e1.mid 
from employee1 e1
   inner join employee1 e2 on e1.eid = e2.mid
   inner join employee1 e3 on e3.mid = e2.eid

But I couldn't find how this query is working. It's giving me the correct answer but, how I don't know.

Can anyone explain how this query is executing step by step. Thanks in advance.

Upvotes: 1

Views: 138

Answers (1)

xQbert
xQbert

Reputation: 35323

My initial thought was this was going up the chain; it looks like it's going down the chain after closer review. So each manager must have at least 2 levels of subordinates before that manager could be returned. If they do not have at least two levels; the manager would not be returned.

So the query joins to employee1 table 2 times each time on MID assuming MID is managerID. As each join is an INNER Join only managers who have employees and employees who have employees will be returned. This means only managers who have at least 2 levels of employees below them will be returned; and of those only distinct employees will be returned.

Break it down:

The 1st join will return all managers and their employees . The 2nd join will return all managers and their employees

So the results will be a distinct list of all managers from the main employee table but only if they have employees, and their employees has a employee due to the nature of inner join only returning records if they exist in the associated table.

There are other ways to do this as well using recursive CTE's instead of having to use a separate join for each level you want to track; thus negating the need to add joins for each level required.

Rextester example

 With CTE AS (
   SELECT eid EID , name NAME , mid MID, 1 as Depth
   FROM EMPLOYEE1
   WHERE MID is not null
   
   UNION ALL
   --This union all is part of the recursion magic

   SELECT E1.eid,e1.name,e1.mid, Depth+1
   FROM Employee1 E1
   --This is where it gets really weird.  We're doing a join to the CTE itself from within the CTE... That's recursion.
   INNER JOIN CTE E2
    on E2.MID=E1.EID
   
   )
          SELECT Distinct * FROM CTE
          WHERE DEPTH = 3
          
          
   ;

Resulting in:

+----+-----+------+------+-------+
|    | EID | NAME | MID  | Depth |
+----+-----+------+------+-------+
|  1 |   3 | Bob  | NULL |     3 |
+----+-----+------+------+-------+

Upvotes: 1

Related Questions