family tree sql query

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

Craft CMS Meta

Q&A about the site for administrators, end users, developers and designers for Craft CMS

DECLARE @FamilyTree TABLE(ID int, Name Varchar(50), MotherID int, FatherID int);
INSERT INTO @FamilyTree
(ID,  MotherID, FatherID,   Name) VALUES
(1,   NULL,    NULL,    'My Fathers Father'),
(2,   NULL,    NULL,    'My Fathers Mother'),
(3,   10, 9,  'My Mother'),
(4,   2,   1,   'My Father'),
(5,   3,   null,   'Me'),
(6,   12, 11,  'My wife'),
(7,   3,   4,   'My Brother'),
(8,   6,   5,   'My son'),
(9,   NULL, NULL, 'My Mother Father'),
(10, NULL, NULL, 'My Mother 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
     left outer JOIN @FamilyTree c ON ft.FatherID = c.ID
     left outer JOIN @FamilyTree c2 ON ft.MotherID = c2.ID
    WHERE ft.ID = 5
    UNION ALL
    SELECT 
      f.ID, 
      f.Name AS ParentName, 
      f.MotherID, 
      f.FatherID, 
      c3.Name AS FatherName, 
      c2.Name AS MotherName, 
      Level + 1
    FROM @FamilyTree AS f
    INNER JOIN FamilyCTE c ON F.ID = c.FatherID
    INNER JOIN @FamilyTree c2 ON f.MotherID = c2.ID
    INNER JOIN @FamilyTree c3 ON f.FatherID = c3.ID
    UNION ALL
    SELECT 
      f.ID, 
      f.Name AS ParentName, 
      f.MotherID, 
      f.FatherID, 
      c2.Name AS FatherName, 
      c3.Name AS MotherName, 
      Level + 1
    FROM @FamilyTree AS f
    INNER JOIN FamilyCTE c ON F.ID = c.MotherID
    INNER JOIN @FamilyTree c2 ON f.FatherID = c2.ID
    INNER JOIN @FamilyTree c3 ON f.MotherID = c3.ID
)

SELECT * FROM FamilyCTE;

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results