-- Group-UnconnectedPersons.sql
/*
2014-01-23 Tom Holden ve3meo
2014-01-24 rev changed table name to more generic

Creates or updates a group named "SQL: Unconnected Persons" of persons
having no parent, no spouse, nor a child in the database
*/

/*
Step 1
Create a temporary table xGroupTempTable of PersonIDs (RINs) 
of those who have neither a parent nor a spouse nor a child. 
*/
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
		);

/* 
Step 2
Create or update a group named "SQL: Unconnected Persons" of persons
in a temp table xGroupTempTable created by Step 1
*/
-- 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
	);