http://stackoverflow.com/questions/13171931/family-tree-sql-query
Q&A for scientists using computers to solve scientific problems
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 WHERE FatherID IS NULL AND MotherID IS NULL AND ID = 1 UNION ALL SELECT f.ID, f.Name AS ParentName, f.MotherID, f.FatherID, c.Name AS FatherName, c2.Name 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, Name, Level FROM FamilyCTE