Group+-+Unconnected+Persons

=Problem= toc I have some experience with SQL but would like to create a query that would create a named group of Orphans that would contain the people in the file that have no parents, spouses, or children. These people are most likely remnants from deleting trees in my old program that I used. Another reason is that one of my gedcoms had family information missing so the entire file imported as individuals that were unconnected.

In the sample file there is only one orphaned person in the file. In my personal file there are actually hundreds of them. Since the file is large, I would like to be able to run the query that would update the named group as I work through merging two large files (over 300,000 people) so I can try to keep track of who is still orphaned in the tree.



=A Solution= This approach involves two steps of development: They are combined into a single script downloadable from the bottom of the page. It is efficient: in a database of 160,000 people, it created a group of the 1000 unconnected persons in ~3 seconds. It takes longer than that for RootsMagic to initially display the group in the sidebar; People View displays the group very quickly.
 * 1) Script to store the RIN (PersonID) of these so-called "Orphans" in a temporary table
 * 2) Script to create/update a Named Group from the temp table

A message about this solution has been posted to the RootsMagic Forums at [].

The "Orphans" Table
code format="sql" -- Persons-NoParentNoSpouseNoChild.sql /* 2014-01-23 Tom Holden ve3meo 2014-01-24 rev changed table name to more generic

Creates a temporary table xGroupTempTable of PersonIDs (RINs) of those who have neither a parent nor a spouse nor a child. To be used with another procedure to update a named group. DROP TABLE IF EXISTS xGroupTempTable;

CREATE TEMP TABLE IF NOT EXISTS xGroupTempTable AS   SELECT * FROM (       - Persons not in FamilyTable, either no spouse or no child        SELECT PersonID        FROM PersonTable

EXCEPT

SELECT * FROM (           SELECT FatherID AS PersonID            FROM FamilyTable

UNION

SELECT MotherID AS PersonID FROM FamilyTable )       ) NATURAL INNER JOIN (       -- persons with no parent        SELECT PersonID        FROM PersonTable

EXCEPT

SELECT ChildID FROM ChildTable ); code

Try the above script. Examine the temp table and look up some persons identified therein using RootsMagic to confirm that it is working as expected.

The "Orphans" Named Group
This script was extracted and adapted to work with the above script from Group - Persons with Text Dates. It creates and maintains a RM Group named "SQL: Unconnected Persons" from the temporary table xGroupTempTable.

After running this script with RootsMagic open simultaneously on the same database, it is necessary to refresh the Sidebar Group display. One does so by selecting a different group and re-selecting "SQL: Unconnected Persons". Unfortunately, if this is the only group in the Groups list, you may have to close and reopen the database to refresh the list of members. code format="sql" -- Group-UnconnectedPersons.sql /* 2014-01-24 Tom Holden ve3meo Creates and updates a named group of persons having no parent, no spouse, nor a child in the database from a temp table xGroupTempTable created by another script -- Create Named Group if it does not exist 'SQL: Unconnected Persons' INSERT OR IGNORE INTO LabelTable VALUES (   ( SELECT LabelID FROM LabelTable WHERE LabelName LIKE 'SQL: Unconnected Persons' )   ,0    ,(        SELECT ifnull(MAX(LabelValue), 0) + 1 FROM LabelTable ) -- ifnull needed if LabelTable is empty   ,'SQL: Unconnected Persons'    ,'SQLite query'    );

-- Delete all members of the named group DELETE FROM GroupTable WHERE GroupID = (       SELECT LabelValue        FROM LabelTable        WHERE LabelName LIKE 'SQL: Unconnected Persons'        );

-- Add members to the named group INSERT INTO GroupTable SELECT NULL ,(       SELECT LabelValue        FROM LabelTable        WHERE LabelName LIKE 'SQL: Unconnected Persons'        ) ,PersonID AS StartID ,PersonID AS EndID FROM (   SELECT DISTINCT PersonID    FROM xGroupTempTable    ); code

Download complete script
This file combines the two scripts into one so that a single pass takes care of updating the group.