Group+-+Ancestors

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

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