Group – Unconnected Persons #namedgroup

Problem

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.

sample data.rmgc

A Solution

This approach involves two steps of development:

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

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.

A message about this solution has been posted to the RootsMagic Forums at http://forums.rootsmagic.com/index.php?/topic/13326-dynamic-group-for-unconnected-persons/.

The “Orphans” Table

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

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.

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

Download complete script

This file combines the two scripts into one so that a single pass takes care of updating the group. Group-UnconnectedPersons.sql

Discussions & comments from Wikispaces site