Group+-+Descendants

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.).

code format="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 ); code