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 enthusiasts and scholars of mythology and folklore

DECLARE @FamilyTree TABLE(ID int, BirimAd Varchar(50), MotherID int, FatherID int);
INSERT INTO @FamilyTree
(ID,  MotherID, FatherID,   BirimAd) 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, 1 AS Level
    FROM @FamilyTree
    WHERE FatherID IS NULL 
      AND MotherID IS NULL
      AND ID = 1
    UNION ALL
    SELECT 
      f.ID, 
      f.BirimAd AS ParentName, 
      f.MotherID, 
      f.FatherID, 
      c.BirimAd AS FatherName, 
      c2.BirimAd AS MotherName, 
      Level + 1
    FROM @FamilyTree AS F
    INNER JOIN FamilyCTE c ON F.FatherID = c.ID
    INNER JOIN @FamilyTree c2 ON f.MotherID = c2.ID
)

SELECT ID, MotherID, FatherID, BirimAd,FatherName, MotherName,Level 
FROM FamilyCTE

fork query

edited mar 2 13 anonymous

created nov 1 12 Mahmoud Gamal