0

Please login or register to vote for this query.

(click on this box to dismiss)

http://stackoverflow.com/questions/13171931/family-tree-sql-query

Q&A for Ubuntu users and developers

DECLARE @FamilyTree TABLE(ID int, Name Varchar(50), MotherID int, FatherID int);
INSERT INTO @FamilyTree
(ID,  MotherID, FatherID,   Name) VALUES
(1,   NULL,    NULL,    'My Grand Father'),
(2,   NULL,    NULL,    'My Grand Mother'),
(3,   10, 9,  'My Mother'),
(4,   2,   1,   'My Father'),
(5,   3,   4,   'Me'),
(6,   12, 11,  'My wife'),
(7,   3,   4,   'My Brother'),
(8,   6,   5,   'My son'),
(9,   NULL, NULL, 'My Mother Grand Father'),
(10, NULL, NULL, 'My Mother Grand Mother'),
(11, NULL, NULL, 'My Wife Grand Father'),
(12, NULL, NULL, 'My Wife Grand Mother');

;WITH FamilyCTE
AS
(
    SELECT 
      ft.*, 
      c.name as FatherName,  
      c2.name as MotherName, 
      0 AS Level
    FROM @FamilyTree ft
    INNER JOIN @FamilyTree c ON ft.FatherID = c.ID
    INNER JOIN @FamilyTree c2 ON ft.MotherID = c2.ID
    WHERE ft.ID = 5

)

SELECT * FROM FamilyCTE;

fork query

edited mar 12 jon.doe44011

created nov 1 12 Mahmoud Gamal