November 10, 2006 at 7:27 am
hi. i got this book, "sql server 2005 for developers" by Peter DeBetta. there is a query in the first chapter that shows you how to do a recursive query, yet i have no idea how it works! i have disected it but cant figure it out. i think maby its the UNION ALL , or the WITH thats baffeling me. can anyone explain it to me? it is to be used on the Adventureworks database that comes wtih sql server 2005. thanks all.
with EmpCTE (EmpID, EmpName, MgrID, Level)
AS
(Select e.employeeID,ec.lastName,e.managerID,1
from HumanResources.employee as e
inner join Person.contact as EC on e.contactID = EC.contactID
where managerID is NULL
UNION ALL
Select e.employeeID, EC.lastName,e.ManagerID, level +1
FROM HumanResources.employee as E
inner join person.contact as EC on e.contactID = EC.contactID
inner join EmpCTE on EmpCTE.empId = e.managerID
where level <= 5 the query first gets the top level manager, and then gets all of the managers, sub managers, down as many levels as they go. manager is an employee and also an entry in a column in the employee table ( circular reference i think its called?)
November 10, 2006 at 10:52 am
To break it down:
With is part of the syntax of creating a Common Table Expression
http://www.databasejournal.com/features/mssql/article.php/3502676
If you look at the bit between the ()'s there are two select statements.
UNION ALL is combining the results of the two selects into one result set. Union ALL differs from UNION (which also combines the results of two results sets into one) by the fact that union all will include duplicate rows, union will eliminate them.
What makes that query recursive is that CTE is joined to itself.
inner join EmpCTE on EmpCTE.empId = e.managerID
It's the recursive logic that allows it to return all the higherarchy (managers, employees etc)
SQL guy and Houston Magician
November 11, 2006 at 3:43 pm
To add to the previous poster you got it in the first chapter because the writer himself probably does not understand the complexity of it, these are the new virtual views or in memory views from ANSI SQL. You can have UNION in the first half and second half but they cannot be combined by UNION because as the other poster said UNION performs implict distinct. That is what the UNION ALL does it connects sections.
And no there is a default of 100 level deep but it can go up to very high after the default. I think you will get better info on the subject in the BOL(books online) under Common Table Expressions. Hope this helps.
Kind regards,
Gift Peddie
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply