x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Because you're mine, I walk the line

....ummm, well, that "line" would be walking through a line in a family tree.  I saw a post the other the day that asked, if I know a relative somewhere in a families "lineage", how can I find the entire family tree from top to bottom.

Well here's a hack to do that...don't ask for effecienciey.  This for SQL Server 2000.  I have to determine if CTE's in 2k5 can come up with a better solution, but here it is for now.

CREATE TABLE Parent (
   ID_PK int IDENTITY(1,1)
 , [Name] varchar(20)
 , PhoneNum varchar(20)
 , Address varchar(30))

CREATE TABLE Child (
   ID_PK int
 , ParentID_FK int)
GO

INSERT INTO Parent([Name],PhoneNum, Address)
SELECT 'Annie',     '111-111-1111', '1st Street' UNION ALL
SELECT 'Bob',       '222-222-2222', '2nd Street' UNION ALL
SELECT 'Cathy',     '333-333-3333', '3rd Street' UNION ALL
SELECT 'Don',       '444-444-4444', '4th Street' UNION ALL
SELECT 'Emily',     '555-555-5555', '5th Street' UNION ALL
SELECT 'Frank',     '666-666-6666', '6th Street' UNION ALL
SELECT 'Georgette', '777-777-7777', '7th Street' UNION ALL
SELECT 'Harry',     '888-888-8888', '8th Street'

INSERT INTO Child(ID_PK, ParentID_FK)
SELECT 1, null UNION ALL
SELECT 2, 1    UNION ALL
SELECT 3, 2    UNION ALL
SELECT 4, 3    UNION ALL
SELECT 5, null UNION ALL
SELECT 6, 5    UNION ALL
SELECT 7, 6    UNION ALL
SELECT 8, 7
GO

SELECT * FROM Parent p LEFT JOIN Child c ON p.ID_PK = c.ID_PK
GO


CREATE FUNCTION udf_FindTree (@Child varchar(20))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @p int, @p_save int, @rs varchar(8000)
SELECT @p = 0, @p_save = 0
SELECT @p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
 WHERE [Name] = @Child
--Loop Until @@rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @p)
  BEGIN
 SELECT @p_save = @p
 SELECT @p = ParentID_FK FROM Child c WHERE ID_PK = @p_save
-- The Last assignement is the top Parent
  END
--Now Walk from the top Down until @@rowcount = 0
SELECT @p = @p_save
SELECT @rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @p)
  BEGIN
 SELECT @rs = @rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @p
 SELECT @p = ID_PK FROM Child WHERE ParentID_FK = @p
  END
RETURN @rs
END
GO

SELECT dbo.udf_FindTree('Cathy')
GO

SELECT * FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
 WHERE [Name] = 'Cathy'
GO

CREATE PROC usp_FindTree @Child varchar(20)
AS
SET NOCOUNT ON
DECLARE @p int, @p_save int, @rs varchar(8000)
SELECT @p = 0, @p_save = 0
SELECT @p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
 WHERE [Name] = @Child
--Loop Until @@rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @p)
  BEGIN
 SELECT @p_save = @p
 SELECT @p = ParentID_FK FROM Child c WHERE ID_PK = @p_save
-- The Last assignement is the top Parent
  END
--Now Walk from the top Down until @@rowcount = 0
SELECT @p = @p_save
SELECT @rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @p)
  BEGIN
 SELECT @rs = @rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @p
 SELECT @p = ID_PK FROM Child WHERE ParentID_FK = @p
  END
SELECT @rs AS rs
SET NOCOUNT OFF
GO

EXEC usp_FindTree 'Cathy'
GO

DROP PROC usp_FindTree
DROP Function udf_FindTree
DROP TABLE Parent, Child
GO

Print | posted on Thursday, July 26, 2007 3:45 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Because you're mine, I walk the line

This is how you do it.

CREATE PROC usp_CTEFindTree @Child varchar(20)
AS
SET NOCOUNT ON;
WITH FamTree ( ID_PK, ParentID_FK, LVL)
AS
(
SELECT ID_PK, ParentID_FK, 1 Lvl
FROM child
WHERE ID_PK IN ( SELECT ID_PK FROM Parent
WHERE [Name] = @Child)
UNION ALL
SELECT Child.ID_PK, Child.ParentID_FK, 1 + Lvl Lvl
FROM child JOIN FamTree
ON Famtree.ParentID_FK = child.ID_PK
)
SELECT Parent.*
FROM Parent JOIN FamTree
ON Parent.ID_PK = FamTree.ID_PK
ORDER BY Lvl DESC
GO

EXEC usp_CTEFindTree 'Cathy'
go
7/27/2007 9:11 AM | Siva G
Gravatar

# re: Because you're mine, I walk the line

The script from Siva G produces the error message:
"Incorrect syntax near the keyword 'WITH'."
7/30/2007 1:31 AM | Erick Ligeyi Litswa
Gravatar

# re: Because you're mine, I walk the line

2005's recursive common table expression - maybe you get an error if compatibility < 90 - otherwise, it works.
7/30/2007 10:40 AM | angelia
Gravatar

# re: Because you're mine, I walk the line

Look up Joe Celko SQL Trees
8/12/2007 8:53 PM | Valter Borges
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET