http://stackoverflow.com/questions/13171931/family-tree-sql-query
Q&A for Ubuntu users and developers
DECLARE @FamilyTree TABLE(ID int, Name Varchar(50), MotherID int, FatherID int); INSERT INTO @FamilyTree (ID, MotherID, FatherID, Name) VALUES (1, NULL, NULL, 'My Fathers Father'), (2, NULL, NULL, 'My Fathers 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 Father'), (10, NULL, NULL, 'My Mother Mother'), (11, NULL, NULL, 'My Wife Grand Father'), (12, NULL, NULL, 'My Wife Grand Mother'); ;WITH FamilyCTE AS ( SELECT ft.*, c.name as FatherName, c2.name as MotherName, 0 AS Level FROM @FamilyTree ft left outer JOIN @FamilyTree c ON ft.FatherID = c.ID left outer JOIN @FamilyTree c2 ON ft.MotherID = c2.ID WHERE ft.ID = 5 UNION ALL SELECT f.ID, f.Name AS ParentName, f.MotherID, f.FatherID, c3.Name AS FatherName, c2.Name AS MotherName, Level + 1 FROM @FamilyTree AS f INNER JOIN FamilyCTE c ON F.ID = c.FatherID INNER JOIN @FamilyTree c2 ON f.MotherID = c2.ID INNER JOIN @FamilyTree c3 ON f.FatherID = c3.ID UNION ALL SELECT f.ID, f.Name AS ParentName, f.MotherID, f.FatherID, c2.Name AS FatherName, c3.Name AS MotherName, Level + 1 FROM @FamilyTree AS f INNER JOIN FamilyCTE c ON F.ID = c.MotherID INNER JOIN @FamilyTree c2 ON f.FatherID = c2.ID INNER JOIN @FamilyTree c3 ON f.MotherID = c3.ID ) SELECT * FROM FamilyCTE;