How the following SQL query is working?

Question

How is the given query correct as it is using T1 inside the with clause and T1 is declared after the clause within WITH is completed.

WITH T1(Emp,Manager,Salary) AS 
( 
SELECT tt2.[Emp],tt2.[Manager],tt2.[Salary] 
FROM [YourTable] AS tt1 
RIGHT OUTER JOIN [YourTable] AS tt2 ON tt1.[Emp]=tt2.[Manager] 
WHERE tt1.[Emp] is NULL 
UNION ALL 
SELECT r.[Emp],T1.[Manager],r.[Salary] 
FROM [YourTable] AS r 
INNER JOIN T1 ON r.[Manager]=T1.[Emp] 
) 
SELECT [Manager],SUM([Salary]) AS Salary 
FROM T1 
GROUP BY [Manager] 
ORDER BY SUM([Salary]) DESC

Above query is answer to following question -

I have table with columns (Employee, Manager, Salary). Need to calculate aggregate salary for all employees corresponding to top-level managers in one SQL. For example

Input table is : 
Emp Manager Salary 
A   T   10 
B   A   11 
C   F   13 
D   B   5

Result should be :

Top-Lvl Manager Salary(agg) 
T   26 
F   13

Manager-Employee layering can go multiple levels.

I removed the incompatible database tags. Please tag with the database you are really using. — Gordon Linoff, Dec 20 '16 at 11:37
This query has been built using CTE(Comman Table Expression). For more info on this, please go through this link-https://www.codeproject.com/Articles/275645/CTE-In-SQL-Server — LogicalDesk, Dec 20 '16 at 11:41

score 4 · Accepted Answer · answered Dec 20 '16 at 12:07

This is a recursive query. The part before UNION ALL gets the base records. The part after it recursively gets more rows attatched to the former.

The first part is confusingly written. It is an anti-join pattern inplemented even with a right outer join which many consider hard to read. It merely means this:

select emp, manager, salary 
from yourtable
where manager not in (select emp from yourtable);

So you get all employees that have no manager (i.e. the super managers).

With the part after UNION ALL you get their subordinates and the subordinates of these etc. A hierarchical query.

At last in

SELECT [Manager],SUM([Salary]) AS Salary 
FROM T1 
GROUP BY [Manager] 
ORDER BY SUM([Salary]) DESC

you use those rows to get a cumulated salary per manager.

You can read up on recursive queries in SQL Server here: https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx.

John Cappelletti · Answer 2 · 2016-12-20T12:40:24.713

EDIT - Less over-kill

Declare @YourTable table (Emp varchar(25),Manager varchar(25),Salary int)
Insert into @YourTable values 
('A','T',10),
('B','A',11), 
('C','F',13),
('D','B',5) 

;with cteP as (
      Select Seq  = cast(1000+Row_Number() over (Order by Emp) as varchar(500))
            ,Emp=Manager
            ,Manager=cast(null as varchar(25))
            ,Lvl=1
            ,Salary = 0
      From  @YourTable 
      Where Manager Not In (Select Distinct Emp From @YourTable)
      Union  All
      Select Seq  = cast(concat(p.Seq,'.',1000+Row_Number() over (Order by r.Emp)) as varchar(500))
            ,r.Emp
            ,r.Manager
            ,p.Lvl+1
            ,r.Salary
      From   @YourTable r
      Join   cteP p on r.Manager = p.Emp)
Select TopLvl = A.Emp
      ,Salary = sum(B.Salary)
 from cteP A
 Join cteP B on (B.Seq Like A.Seq+'%')
 Where A.Lvl=1
 Group By A.Emp

Returns

TopLvl  Salary
F       13
T       26

score 1 · Answer 3 · answered Dec 20 '16 at 11:42

1

Inside with T1 references to INNER JOIN T1 ON r.[Manager]=T1.[Emp] which is probably table in your DB. Outside with T1 references to result of with statement.

answered Dec 20 '16 at 11:42

Kacper

4,773
2
15
31

I don't think it is that way. There is no other table named T1. It is using T1 because it need to do it recursively to single layering for Manager-Employee multi level layering. – Shubham Jain Dec 20 '16 at 11:45
If you're sure that there is no T1 table nor synonym it means Thorsten's answer is what works here. – Kacper Dec 20 '16 at 12:43

How the following SQL query is working?

3 Answers3