This query supersedes Ancestors Named Group which was limited to 12 generations due to its dependence on the non-recursive Ancestors Query. This new query is unlimited as it is based on Recursive Queries – Ancestors and Descendants which exploit capability introduced to SQLite in Feb 2014. When run, you will be prompted for the Record Number (RIN) of the person whose ancestors are be in the group and for the type of ancestral relationship – birth only (bloodline) or all inclusive (adoptive, step, etc.).
-- Group-Ancestors.sql /* 2014-04-15 Tom Holden ve3meo Creates and updates a named group of ancestors of a person using an unlimited recursive tree climb. Does not include starting person in the group. An example of an auto-generated group name is "!Anc: Holden, Robert Alexander-155." but the query will update any group with a name beginning "!Anc:" followed by anything to "-nn.", where nn is the RIN number followed by the period character. Requires support for SQLite 3.8.3 or later and named parameters. Developed and tested with SQLite Expert Personal 184.108.40.2066 */ -- Register the RIN of the starting person for following queries -- to preclude repeated entry DROP TABLE IF EXISTS xGroupIDTable; CREATE TEMP TABLE IF NOT EXISTS xGroupIDTable AS SELECT @StartRIN AS RIN ,-- named parameter is prompted for user entry NULL AS GroupID -- to be filled in after finding or creating the group label ; -- Create Named Group if it does not exist '!Anc: Surname, Given-RIN.' INSERT OR IGNORE INTO LabelTable VALUES ( ( SELECT LabelID FROM LabelTable WHERE LabelName LIKE '!Anc:%-' || ( SELECT RIN FROM xGroupIDTable ) || '.' ) ,0 ,( SELECT ifnull(MAX(LabelValue), 0) + 1 FROM LabelTable ) -- ifnull() needed if LabelTable is empty ,'!Anc: ' || ( SELECT Surname || ', ' || Given FROM NameTable WHERE OwnerID = ( SELECT RIN FROM xGroupIDTable ) AND + IsPrimary ) || '-' || ( SELECT RIN FROM xGroupIDTable ) || '.' ,'SQLite query' ); -- register GroupId in the temp table for following queries UPDATE xGroupIDTable SET GroupID = ( SELECT LabelValue FROM LabelTable WHERE LabelName LIKE '!Anc:%-' || ( SELECT RIN FROM xGroupIDTable ) || '.' ); -- Delete all members of the named group DELETE FROM GroupTable WHERE GroupID = ( SELECT GroupID FROM xGroupIDTable ); -- Add members to the named group INSERT INTO GroupTable SELECT NULL ,( SELECT GroupID FROM xGroupIDTable ) ,AncestorID AS StartID ,AncestorID AS EndID FROM ( 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 = ( SELECT RIN FROM xGroupIDTable ) --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 );