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

Mythology

Q&A for enthusiasts and scholars of mythology and folklore

DECLARE @FamilyTree TABLE(BirimId int, BirimAd Varchar(50), ParentId int, RootId int);
INSERT INTO @FamilyTree
(BirimId,  ParentId, RootId,   BirimAd) VALUES
(1,   NULL,    NULL,    'İSTANBUL'),
(2,   NULL,    NULL,    'BOŞ'),
(3,   10, 9,  'My Mother'),
(4,   2,   1,   'BOŞ'),
(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 RootId IS NULL 
      AND ParentId IS NULL
      AND BirimId = 1
    UNION ALL
    SELECT 
      f.BirimId, 
      f.BirimAd AS ParentName, 
      f.ParentId, 
      f.RootId, 
      c.BirimAd AS FatherName, 
      c2.BirimAd AS MotherName, 
      Level + 1
    FROM @FamilyTree AS F
    INNER JOIN FamilyCTE c ON F.RootId = c.BirimId
    INNER JOIN @FamilyTree c2 ON f.ParentId = c2.BirimId
)

SELECT BirimId, ParentId, RootId, BirimAd,FatherName, MotherName,Level 
FROM FamilyCTE

Enter Parameters

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