x002548's Blog

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

Thursday, July 26, 2007

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

posted @ Thursday, July 26, 2007 3:45 PM | Feedback (4) | Filed Under [ SQL Server ]

Powered by:
Powered By Subtext Powered By ASP.NET