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

Joomla Meta

Q&A about the site for Joomla! administrators, users, developers and designers

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 
      *, 
      CAST(NULL AS VARCHAR(50)) AS FatherName,  
      CAST(NULL AS VARCHAR(50)) AS MotherName, 0 AS Level
    FROM @FamilyTree
    
)

SELECT ID, MotherID, FatherID, Name, Level 
FROM FamilyCTE

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results