How do I write a query to get the path from ancestor to node in a tree?

[Tzvika Barenholz]

Hi all. Here's my problem: I have a tree linking macaddresses (bigints)
in a tree structure. i want to get the path from node a to b.

create table tree1(father bigint , child bigint);

insert into tree1 (father,child) values (100,200);
insert into tree1 (father,child) values (100,300);
insert into tree1 (father,child) values (100,400);
insert into tree1 (father,child) values (200,2000);
insert into tree1 (father,child) values (200,3000);
insert into tree1 (father,child) values (100,4000);
insert into tree1 (father,child) values (2000,11111);

you can see that 100 --> 200 --> 2000 --> 11111

select * from tree1
what i would like is a query that given two parameters returns the path
between
them, in the case of 100,11111 i want to get
100
200
2000
11111

if possible as different rows, but columns will do to.

of course i do not know the legnth of the path. it can be very big

thx in advance
Tzvika

[Jens]

Try that:

IF OBJECT_ID('Tree') IS NOT NULL
DROP Table Tree

CREATE TABLE Tree
(
Parent INT,
Child INT
)

INSERT INTO Tree
SELECT 100,200
INSERT INTO Tree
SELECT 100,300
INSERT INTO Tree
SELECT 100,400
INSERT INTO Tree
SELECT 400,500
INSERT INTO Tree
SELECT 500,1000

DROP FUNCTION getHierarchy

GO

CREATE FUNCTION getHierarchy
(
@child INT
)
RETURNS @Hierarchy TABLE
(
Path INT
)
AS
BEGIN

IF NOT EXISTS (SELECT * from Tree where child = @child)
BEGIN
INSERT INTO @Hierarchy
SELECT NULL
SET @child = NULL
END

WHILE (@Child IS NOT NULL OR LEN(@Child) = 0)
BEGIN
INSERT INTO @Hierarchy
SELECT @Child

SELECT @Child = Parent from Tree where child = @child
IF @@ROWCOUNT = 0 SET @Child = NULL
END

RETURN
END

GO

Select * from dbo.getHierarchy(500)

HTH, Jens Suesmeyer

---
http://www.sqlserver2005.de
---

[--CELKO--]

For now, Google "nested sets model" for a better way. Then get a copy
of TREES & HIERARCHIES IN SQL for other ways to model this data.

[Tzvika Barenholz]

Thanks Joe, Jens. Good stuff.
Thanks again.
Tzvika