Group – Descendants #descendants #namedgroup #recursive

This query creates or updates a named group of descendants of a given person. This new query is unlimited in the number of generations 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 descendants are to be in the group and for the type of descendant relationship – birth only (bloodline) or all inclusive (adoptive, step, etc.).

Group-Descendants.sql

-- Group-Descendants.sql
/*
2014-04-15 Tom Holden ve3meo
Creates and updates a named group of descendants 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 "!Desc: Holden, Robert Alexander-155." but the
query will update any group with a name beginning "!Desc:" 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 '!Desc: Surname, Given-RIN.'
INSERT
    OR IGNORE
INTO LabelTable
VALUES (
    (
        SELECT LabelID
        FROM LabelTable
        WHERE LabelName LIKE '!Desc:%-' || (
                SELECT RIN
                FROM xGroupIDTable
                ) || '.'
        )
    ,0
    ,(
        SELECT ifnull(MAX(LabelValue), 0) + 1
        FROM LabelTable
        ) -- ifnull() needed if LabelTable is empty
    ,'!Desc: ' || (
        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 '!Desc:%-' || (
                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
        )
    ,DescendantID AS StartID
    ,DescendantID AS EndID
FROM (
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)
     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)
     ),
  descendant_of_person(DescendantID) AS
    (SELECT ChildID FROM child_of
       WHERE ParentID=(
                    SELECT RIN
                    FROM xGroupIDTable
                    ) --RIN of starting person entered at runtime
     UNION --ALL
     SELECT ChildID FROM child_of
       INNER JOIN descendant_of_person ON ParentID = DescendantID)
SELECT DescendantID FROM descendant_of_person, PersonTable
 WHERE descendant_of_person.DescendantID=PersonTable.PersonID
);