Group – Ancestors #ancestors #namedgroup #recursive

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

-- 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 3.5.36.2456
*/
-- 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
    );