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.
| Author |
Topic |
|
skip_203
Starting Member
4 Posts |
 Posted - 2005-12-08 : 00:17:30
|
| I've been trying every which way to figure out what's happening here, but I'm at my wits end and any pointers would be greately appreciated:I'm attempting to walk an org hierarchy in an employee table using a SQL 2005 common table expression, but the results I get back are less than in the source file.Source table schema (generic employee table) ID (int), ManagerID (int), Email varchar(55), ManagerEmail varchar(55)CTE QueryWITH getStaff( ID, ManagerEmail, Email, Level ) AS( SELECT ID, ManagerEmail, Email, 1 as Level FROM Employee WHERE ManagerEmail = 'Big CEO' (Top level of Org, of course) UNION ALL SELECT Employee.ManagerEmail, Employee.Email, getStaff.Level +1 as Level FROM Employee INNER JOIN getStaff ON Employee.ManagerID = getStaff.ID)SELECT count(*) from getStaffThe source table (employee) has 100,401) rows in it, but the getStaff CTE query only returns 98,113 rows. I've tried different anchor criteria such as ManagerEmail IS NULL, with no difference. The results I do get back look correct and the levels jive with the org chart. I checked the deltas using a WHERE NOT EXISTS query, and the employee emails found that are not in the getStaff temp table look perfectly normal and have valid ManagerID, ManagerEmail, ID, etc.Has any one else seen this behavior in CTE queries? Probably a bone-head issue on my side, but I can't think of what else to check.Thanks! |
|
|
skip_203
Starting Member
4 Posts |
Posted - 2005-12-08 : 14:45:08
|
quote:
Originally posted by skip_203Probably a bone-head issue on my side, but I can't think of what else to check.
After getting away from the problem overnight, I think I've identified the issue. The CTE query works fine, it was that there were many orphaned employees who had null managers due to turnover which made these employees unreachable by the query. That there are breaks in multiple levels of the org hierachy makes a recursive query difficult, but it's probably time to clean up the data. :-) |
 |
|
|
|
|
|
|
|
SQL-2005, recursive CTE not returning all rows