-- AncestorsRecursive.sql
/*
2014-04-14 Tom Holden ve3meo

Generates the list of RINs for the ancestors of a person.

Requires support not only for SQLite 3.8.3 or later but also
for named parameters for user input of the RIN of the starting person
and choice of birth only or all relationships. 

Developed and tested with current SQLite Expert Personal 3.5.36.2456

Uses the WITH RECURSIVE syntax introduced in SQLite 3.8.3 2014-02-03
modelled on http://www.sqlite.org/lang_with.html example
and complement of DescendantsRecursive.sql
*/

WITH RECURSIVE
  parent_of(ChildID, ParentID) AS
    (SELECT PersonID, FatherID AS ParentID FROM PersonTable 
       LEFT JOIN ChildTable ON PersonID=ChildTable.ChildID 
       LEFT JOIN FamilyTable USING(FamilyID) 
       WHERE 
         CASE $BirthOnly(YN)       
         WHEN 'Y' OR 'y' THEN RelFather=0         
         ELSE 1         
         END       
         --RelFather=0 --birth father (ELSE WHERE 1 to include all relationships)
     UNION
     SELECT PersonID, MotherID AS ParentID FROM PersonTable 
       LEFT JOIN ChildTable ON PersonID=ChildTable.ChildID 
       LEFT JOIN FamilyTable USING(FamilyID) 
       WHERE 
         CASE $BirthOnly(YN)       
         WHEN 'Y' OR 'y' THEN RelMother=0         
         ELSE 1         
         END
         --RelMother=0 --birth mother (ELSE WHERE 1 to include all relationships)
     ),
  ancestor_of_person(AncestorID) AS
    (SELECT ParentID FROM parent_of 
       WHERE ChildID=$Person(RIN) --enter RIN of starting person at runtime
     UNION --ALL
     SELECT ParentID FROM parent_of 
       INNER JOIN ancestor_of_person ON ChildID = AncestorID)
SELECT AncestorID FROM ancestor_of_person, PersonTable
 WHERE ancestor_of_person.AncestorID=PersonTable.PersonID
;