query: child of child of child of...: Which are the descendants?

Hi,
I have a table like this:
tblDescendants
ParentID ChildID
A B
A C
B D
D E
So this table contains parent-child relations, but they can go infinite
long.
What I need is a select-query, that returns me for al the 'original'
parents, their relatiosn with their cildren, grandchildren, and further
descendants etc...
So with this records:
ParentID DescendantID
A B
A C
A D (because D is a child of B which is a child of A)
A E (same reason).
How can I do this? I need to use this select in a join. I have a solution
with a temporary table, but I would prefer not to have to make this
temporary table each time I run my query... I would prefer a solution with
an Indexed View.
Does anybody knows how? Any help will be really appreciated!
Thansk a lot in advance,
PieterHi
Take a look at Itzik Ben-Gan's examples
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
--SQL Server 2005--
With TreeCTE (empid, mgrid, empname, salary, lvl, [path]) as
(
select empid, mgrid, empname, salary, 0 as lvl, cast('' as varchar(200))
as [path]
from Employees
union all
select tc.empid, e.mgrid, tc.empname, tc.salary, lvl + 1,
cast([path]+cast(e.empid as varchar(20))+'/' as varchar(200))
from Employees e
inner join TreeCTE tc on e.empid = tc.mgrid
)
select * from TreeCTE where mgrid = 1
----
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
/*
employee
--
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
"Pieter" <pieterNOSPAMcoucke@.hotmail.com> wrote in message
news:%23orHcToIHHA.1064@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a table like this:
> tblDescendants
> ParentID ChildID
> A B
> A C
> B D
> D E
>
> So this table contains parent-child relations, but they can go infinite
> long.
> What I need is a select-query, that returns me for al the 'original'
> parents, their relatiosn with their cildren, grandchildren, and further
> descendants etc...
> So with this records:
> ParentID DescendantID
> A B
> A C
> A D (because D is a child of B which is a child of A)
> A E (same reason).
> How can I do this? I need to use this select in a join. I have a solution
> with a temporary table, but I would prefer not to have to make this
> temporary table each time I run my query... I would prefer a solution with
> an Indexed View.
> Does anybody knows how? Any help will be really appreciated!
> Thansk a lot in advance,
> Pieter
>|||Ok thanks, putting it all in a function works :-)