http://stackoverflow.com/questions/13171931/family-tree-sql-query
Q&A for scientists using computers to solve scientific problems
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, 2, 1, 'İl Emniyet Müdürlüğü Polikliniği'), (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