/* Ancestors.sql
Builds a table of all the lines of ancestors (RINs) for a given person (RIN),
set as a runtime manual input parameter. If blank or if the SQLite manager does not
support runtime parameters, lists all the ancestral lines for everyone in the
database - avoid doing so because it is a huge result set and very slow.
2011-11-23 by ve3meo
           rev to correct flawed logic that missed many ancestors
*/

SELECT Fam1.PersonID AS Gen1, Fam2.PersonID AS Gen2, Fam3.PersonID AS Gen3, Fam4.PersonID AS Gen4, Fam5.PersonID AS Gen5, Fam6.PersonID AS Gen6, Fam7.PersonID AS Gen7, Fam8.PersonID AS Gen8, Fam9.PersonID AS Gen9, Fam10.PersonID AS Gen10, Fam11.PersonID AS Gen11, Fam12.PersonID AS Gen12, Fam12.FatherID AS Male13, Fam12.MotherID AS Female13
FROM
(
SELECT PersonID, Ch1.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch1 ON PersonID=Ch1.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch1.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch1 ON PersonID=Ch1.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam1
LEFT JOIN
(
SELECT PersonID, Ch2.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch2 ON PersonID=Ch2.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch2.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch2 ON PersonID=Ch2.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam2 
ON Fam1.ParentID=Fam2.PersonID
LEFT JOIN
(
SELECT PersonID, Ch3.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch3 ON PersonID=Ch3.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch3.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch3 ON PersonID=Ch3.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam3
ON Fam2.ParentID=Fam3.PersonID
LEFT JOIN
(
SELECT PersonID, Ch4.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch4 ON PersonID=Ch4.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch4.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch4 ON PersonID=Ch4.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam4
ON Fam3.ParentID=Fam4.PersonID
LEFT JOIN
(
SELECT PersonID, Ch5.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch5 ON PersonID=Ch5.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch5.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch5 ON PersonID=Ch5.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam5
ON Fam4.ParentID=Fam5.PersonID
LEFT JOIN
(
SELECT PersonID, Ch6.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch6 ON PersonID=Ch6.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch6.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch6 ON PersonID=Ch6.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam6
ON Fam5.ParentID=Fam6.PersonID
LEFT JOIN
(
SELECT PersonID, Ch7.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch7 ON PersonID=Ch7.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch7.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch7 ON PersonID=Ch7.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam7
ON Fam6.ParentID=Fam7.PersonID
LEFT JOIN
(
SELECT PersonID, Ch8.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch8 ON PersonID=Ch8.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch8.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch8 ON PersonID=Ch8.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam8
ON Fam7.ParentID=Fam8.PersonID
LEFT JOIN
(
SELECT PersonID, Ch9.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch9 ON PersonID=Ch9.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch9.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch9 ON PersonID=Ch9.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam9
ON Fam8.ParentID=Fam9.PersonID
LEFT JOIN
(
SELECT PersonID, Ch10.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch10 ON PersonID=Ch10.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch10.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch10 ON PersonID=Ch10.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam10
ON Fam9.ParentID=Fam10.PersonID
LEFT JOIN
(
SELECT PersonID, Ch11.ChildID, FatherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch11 ON PersonID=Ch11.ChildID LEFT Join FamilyTable USING (FamilyID)
UNION
SELECT PersonID, Ch11.ChildID, MotherID AS ParentID FROM PersonTable LEFT JOIN ChildTable AS Ch11 ON PersonID=Ch11.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam11
ON Fam10.ParentID=Fam11.PersonID
LEFT JOIN
(
SELECT PersonID, Ch12.ChildID, FatherID, MotherID FROM PersonTable LEFT JOIN ChildTable AS Ch12 ON PersonID=Ch12.ChildID LEFT Join FamilyTable USING (FamilyID)
) AS Fam12
ON Fam11.ParentID=Fam12.PersonID

WHERE
CASE 
WHEN @StartRIN NOT LIKE '' THEN Gen1 = @StartRIN
ELSE 1
END
;