Prev: Tivoli Storage Manager 5.4
Next: Opposite to INNER JOIN
From: tshad on 4 Mar 2010 20:39
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


From: --CELKO-- on 4 Mar 2010 21:33
>> 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.
From: Plamen Ratchev on 4 Mar 2010 22:31
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: Tony Rogerson on 5 Mar 2010 02:48
> No. And the term is fixed point (singular), not anchor. For kicks,
> look up the Ackermann Function.

Huh?

The term is "anchor" - stop trying to confuse people with your own
misunderstandings.

--ROGGIE--

"--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.

From: Dan Guzman on 5 Mar 2010 08:07
> 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
>
>
 |  Next  |  Last
Pages: 1 2
Prev: Tivoli Storage Manager 5.4
Next: Opposite to INNER JOIN