Home > Common Table Expressions, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > TSQL – Common Table Expressions (CTE) – Part 4 – Multiple References

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

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

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: