-- DescendantsRecursive.sql
/*
2014-04-14 Tom Holden ve3meo
rev 2015-11-24 maternal or paternal lines added

Generates the list of RINs for the descendants of a person,
optionally through through their maternal or paternal lines.

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,
choice of birth only or all relationships, and choice of the gender
of the persons (for paternal Y-STR or maternal mtDNA lines). 

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 AncestorsRecursive.sql 
*/

WITH RECURSIVE
  child_of(ParentID, ChildID) AS
    (SELECT PersonID, ChildTable.ChildID FROM PersonTable 
       LEFT JOIN FamilyTable ON PersonID=FatherID        
       LEFT JOIN ChildTable 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)
         AND 
         CASE UPPER($Sex(MF?))         
         WHEN 'M' THEN Sex = 0         
         WHEN 'F' THEN Sex = 1         
         ELSE 1         
         END
     UNION 
     SELECT PersonID, ChildTable.ChildID FROM PersonTable 
       LEFT JOIN FamilyTable ON PersonID=MotherID        
       LEFT JOIN ChildTable 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)         
         AND
         CASE UPPER($Sex(MF?))         
         WHEN 'M' THEN Sex = 0         
         WHEN 'F' THEN Sex = 1         
         ELSE 1         
         END
     ),
  descendant_of_person(DescendantID) AS
    (SELECT ChildID FROM child_of 
       WHERE ParentID=$Person(RIN) --enter RIN of starting person at runtime
     UNION --ALL
     SELECT ChildID FROM child_of 
       INNER JOIN descendant_of_person ON ParentID = DescendantID)
SELECT DescendantID, Sex FROM descendant_of_person, PersonTable
 WHERE descendant_of_person.DescendantID=PersonTable.PersonID 
 AND
         CASE UPPER($Sex(MF?))         
         WHEN 'M' THEN Sex = 0         
         WHEN 'F' THEN Sex = 1         
         ELSE 1         
         END
;