Issue With Common Table Expression

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Issue With Common Table Expression

Author

Topic

SilentCodingOne
Starting Member

20 Posts

Posted - 2008-12-30 : 22:55:49

I'm working on a homework problem that calls for me to use a CTE to analyze a table called NewEmployees which was added to the Northwind database. It is basically a copy of the Employee table structure. I have created a CTE which does pull in the data I'm looking to use in the report however all that comes up are the column headers . I have attached a sample look at what the report is suppose to look like and my current code. Any help would be greatly appreciated.


USE Northwind
GO

WITH EmployeeReport AS
(
SELECT ne.EmployeeID, ne.LastName, ne.FirstName, ne.Title, ne.ReportsTo AS ManagerID,
e.LastName AS ManagerLastName, e.FirstName AS ManagerFirstName , 0 AS level
FROM dbo.NewEmployees ne JOIN
dbo.Employees e ON ne.ReportsTo = e.EmployeeID
WHERE ne.ReportsTo IS NULL
UNION ALL
SELECT ne.EmployeeID, ne.LastName, ne.FirstName, ne.Title, ne.ReportsTo AS ManagerID,
e.LastName AS ManagerLastName, e.FirstName AS ManagerFirstName , ER.level + 1
FROM dbo.NewEmployees ne JOIN
dbo.Employees e ON ne.ReportsTo = e.EmployeeID
INNER JOIN EmployeeReport ER ON ne.ReportsTo = ER.EmployeeID
)  
SELECT * FROM EmployeeReport


EmpID   LName       FName       Level   MngrID  MngrLName   MngrFName 
13  Inoue       George          1   1   Fuller          Andrew  
25  Talon       Rob     1   1   Fuller          Andrew 
37  Evans       Cleave         1    1   Fuller          Andrew  
… 
901 Young       Ward        1   1   Fuller          Andrew  -- row 75 
890 Normal          Mark        2   901 Young       Ward 
891 Orthon         Navid        2   901 Young       Ward

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 03:20:23

try like this



USE Northwind
GO

WITH EmployeeReport AS
(
SELECT ne.EmployeeID, ne.LastName, ne.FirstName, ne.Title, ne.ReportsTo AS ManagerID,
CAST(NULL AS Varchar(100)) AS ManagerLastName, 
CAST(NULL AS Varchar(100)) AS ManagerFirstName , 0 AS level
FROM dbo.NewEmployees ne 
WHERE ne.ReportsTo IS NULL
UNION ALL
SELECT ne.EmployeeID, ne.LastName, ne.FirstName, ne.Title, ne.ReportsTo AS ManagerID,
e.LastName AS ManagerLastName, e.FirstName AS ManagerFirstName , ER.level + 1
FROM dbo.NewEmployees ne JOIN
dbo.Employees e ON ne.ReportsTo = e.EmployeeID
INNER JOIN EmployeeReport ER ON ne.ReportsTo = ER.EmployeeID
)  
SELECT * FROM EmployeeReport

SilentCodingOne
Starting Member

20 Posts

Posted - 2009-01-01 : 01:30:06

Thanks for the tips. The code did not work but it did give me some ideas. I modified the CTE to look like this:


USE Northwind
GO

WITH EmployeeReport AS
(
SELECT ne.EmployeeID, ne.LastName, ne.FirstName, ne.Title, 0 AS level, ne.ReportsTo AS
ManagerID
FROM dbo.NewEmployees ne 
UNION ALL
SELECT ne.EmployeeID, ne.LastName, ne.FirstName, ne.Title, ER.level + 1, ne.ReportsTo AS
ManagerID
FROM dbo.NewEmployees ne INNER JOIN 
EmployeeReport ER ON ne.ReportsTo = ER.EmployeeID
)  
SELECT er.EmployeeID, er.LastName, er.FirstName, er.level, er.ManagerID, 
mr.LastName AS ManagersLastName, mr.FirstName AS ManagersFirstName FROM EmployeeReport er
INNER JOIN EmployeeReport mr
ON er.ManagerID = mr.EmployeeID

This code brings back a number of records but it appears everything is duplicated. Any ideas as to where I'm going wrong?

SilentCodingOne
Starting Member

20 Posts

Posted - 2009-01-01 : 23:30:52

I was able to finally get it to work using something like this:


USE Northwind
GO

WITH EmployeeReport AS
(
SELECT ne.EmployeeID, ne.LastName, ne.FirstName, ne.Title, ne.ReportsTo AS ManagerID, e.LastName AS ManagersLastName, e.FirstName AS ManagersFirstName,
0 AS level
FROM dbo.NewEmployees ne LEFT JOIN dbo.Employees e ON ne.EmployeeID = e.EmployeeID
UNION ALL
SELECT ne.EmployeeID, ne.LastName, ne.FirstName, ne.Title, ne.ReportsTo AS ManagerID,
e.LastName AS ManagerLastName, e.FirstName AS ManagerFirstName , ER.level + 1
FROM dbo.NewEmployees ne JOIN
dbo.Employees e ON ne.ReportsTo = e.EmployeeID
INNER JOIN EmployeeReport ER ON ne.ReportsTo = ER.EmployeeID
)  
SELECT DISTINCT er.EmployeeID, er.LastName, er.FirstName, er.level, er.ManagerID,
mr.LastName AS ManagersLastName, mr.FirstName AS ManagersFirstName FROM EmployeeReport er
INNER JOIN EmployeeReport mr
ON er.ManagerID = mr.EmployeeID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources