CTE with multiple anchors
in [Programming SQL]
|
Prev: Tivoli Storage Manager 5.4
Next: Opposite to INNER JOIN
From: tshad on 5 Mar 2010 10:38 "--CELKO--" <jcelko212(a)earthlink.net> wrote in message news:84235f2c-7beb-44ac-aea0-3b02eecbaba9(a)g28g2000yqh.googlegroups.com... >>> Is there a way to have multiple anchors? << > > No. And the term is fixed point (singular), not anchor. For kicks, > look up the Ackermann Function. Then I guess Microsoft has it wrong: http://msdn.microsoft.com/en-us/library/ms186243.aspx 1.. Invocation of the routine. The first invocation of the recursive CTE consists of one or more CTE_query_definitions joined by UNION ALL, UNION, EXCEPT, or INTERSECT operators. Because these query definitions form the base result set of the CTE structure, they are referred to as ANCHOR members. CTE_query_definitions are considered ANCHOR members unless they reference the CTE itself. All ANCHOR-member query definitions must be positioned before the first recursive member definition, and a UNION ALL operator must be used to join the last ANCHORmember with the first recursive member.
From: tshad on 5 Mar 2010 10:43 In my case, there is no actual starting points but multiple starting points. The file has multiple distribution lists with other lists below them with other lists below them and then the members. BTW, to get the members from the bottom level list, would you get the members from inside the CTE or the statement outside the CTE (outside the brackets)? Thanks, Tom "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message news:r7SdnTYe7-GQ5g3WnZ2dnUVZ_tudnZ2d(a)speakeasy.net... > You should not need multiple anchor queries if you can select all top > level groups in one query. > > -- > Plamen Ratchev > http://www.SQLStudio.com
From: Plamen Ratchev on 5 Mar 2010 10:53 tshad wrote: > In my case, there is no actual starting points but multiple starting points. > > The file has multiple distribution lists with other lists below them with > other lists below them and then the members. > This is fine, you can get multiple starting points in a single query (or you can use multiple queries if needed). > BTW, to get the members from the bottom level list, would you get the > members from inside the CTE or the statement outside the CTE (outside the > brackets)? > You get the bottom level inside the CTE. -- Plamen Ratchev http://www.SQLStudio.com
From: tshad on 5 Mar 2010 14:07
It does. Thanks, Tom "Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message news:CEBFD36E-1992-464B-9FB1-B4DEB5DF1FB5(a)microsoft.com... >> In my group table there are multiple top level groups with other groups >> below and other groups below that. >> >> Is there a way to have multiple anchors? > > Yes, you can have multiple anchor rows (see below). You can also have > multiple anchor CTE query members (SELECTs that don't reference the CTE > itself). All the anchor members are run first, followed by recursive > members. > > DECLARE @MultipleAnchors TABLE( > ParentID int NULL, > ID int NOT NULL PRIMARY KEY); > > INSERT INTO @MultipleAnchors > VALUES > (NULL, 1), > (NULL, 2), > (1, 3), > (1, 4), > (4, 5), > (2, 6), > (6, 7), > (7, 8); > > WITH MultipleAnchorRowsCTE > AS > ( > SELECT ParentID, ID > FROM @MultipleAnchors > WHERE ParentID IS NULL > UNION ALL > SELECT children.ParentID, children.ID > FROM @MultipleAnchors children > JOIN MultipleAnchorRowsCTE macte ON > macte.ID = children.ParentID > ) > SELECT ParentID, ID > FROM MultipleAnchorRowsCTE; > > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > http://weblogs.sqlteam.com/dang/ > > "tshad" <t(a)dslextreme.com> wrote in message > news:#FA09SAvKHA.5940(a)TK2MSFTNGP02.phx.gbl... >> I have a recursive function that is pretty complicated but there are many >> anchors. All the examples I see have only one anchor such as this from: >> >> http://msdn.microsoft.com/en-us/library/ms186243.aspx >> >> ********************************** >> USE AdventureWorks; >> GO >> WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level) >> AS >> ( >> -- Anchor member definition >> SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, >> 0 AS Level >> FROM HumanResources.Employee AS e >> INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh >> ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL >> WHERE ManagerID IS NULL >> UNION ALL >> -- Recursive member definition >> SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, >> Level + 1 >> FROM HumanResources.Employee AS e >> INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh >> ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL >> INNER JOIN DirectReports AS d >> ON e.ManagerID = d.EmployeeID >> ) >> -- Statement that executes the CTE >> SELECT ManagerID, EmployeeID, Title, Level >> FROM DirectReports >> INNER JOIN HumanResources.Department AS dp >> ON DirectReports.DeptID = dp.DepartmentID >> WHERE dp.GroupName = N'Research and Development' OR Level = 0; >> GO >> *********************************** >> >> In my group table there are multiple top level groups with other groups >> below and other groups below that. >> >> Is there a way to have multiple anchors? >> >> Thanks, >> >> Tom >> >> |