Archive

Posts Tagged ‘Self Join’

TSQL – Common Table Expressions (CTE) – Part 4 – Multiple References

April 19, 2011 Leave a comment

A CTE can be referenced multiple time in a single statement, this can be useful in a self-join scenario. age old employee manager example…. consider following data,

image

Now to list employees with their respective managers we need to do a self-join

SELECT      EMP1.EmpID, EMP1.EmpName, EMP1.ManagerID,
            EMP2.EmpName AS [ManagerName]
FROM        dbo.Employees EMP1
LEFT JOIN   dbo.Employees EMP2 ON EMP1.ManagerID = EMP2.EmpID

We can also achieve this by using a CTE,

;WITH EmpCTE AS
(
      SELECT      EmpID, EmpName, ManagerID
      FROM        dbo.Employees
)     SELECT      CTE1.EmpID, CTE1.EmpName, CTE1.ManagerID,
                  CTE2.EmpName AS [ManagerName]
      FROM        EmpCTE CTE1
      LEFT JOIN   EmpCTE CTE2 ON CTE1.ManagerID = CTE2.EmpID

Note that we are referencing the same CTE multiple times in the statement. and the output will be as follows:

image

Now, if you try to use a derived table in this scenario – well, you can’t do it…

SELECT      dTable.EmpID, dTable.EmpName, dTable.ManagerID,
            dTable1.EmpName AS [ManagerName]
FROM        (SELECT     EmpID, EmpName, ManagerID
            FROM  dbo.Employees)AS dTable
INNER JOIN  dTable dTable1 ON dTable.ManagerID = dTable.EmpID

if you try to execute above statement, you will get an error:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'dTable'.

You can download the scripts here.

Check out all five parts:

TSQL – Common Table Expressions (CTE) – Part 0 – Introduction
TSQL – Common Table Expressions (CTE) – Part 1 – Recursive Query

TSQL – Common Table Expressions (CTE) – Part 2 – Simplifying a Query

TSQL – Common Table Expressions (CTE) – Part 3 – GROUP BY calculated column

TSQL – Common Table Expressions (CTE) – Part 4 – Multiple References

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe