-- DeleteByNamedGroup2.sql
/*
2012-10-27 Tom Holden ve3meo
2013-01-21 v2 - deletes groups with name beginning '#DELETE#'

Deletes Persons and Families from PersonTable and FamilyTable respectively for 
all persons in specific Named Groups having a name starting with #DELETE# .

N.B.: This procedure results in many types of 'phantoms' and should be followed by
the procedure DeletePhantoms.sql 
*/
-- Delete Family where one of the spouses is in the Group
-- Fathers or Husbands
DELETE
FROM FamilyTable
WHERE FatherID IN (
		SELECT PersonID
		FROM PersonTable
			,GroupTable
		WHERE PersonID BETWEEN StartID
				AND EndID
			AND GroupID IN (
				SELECT LabelValue
				FROM LabelTable
				WHERE UPPER(LabelName) LIKE '#DELETE#%'
				)
		);

-- Mothers or Wives
DELETE
FROM FamilyTable
WHERE MotherID IN (
		SELECT PersonID
		FROM PersonTable
			,GroupTable
		WHERE PersonID BETWEEN StartID
				AND EndID
			AND GroupID IN (
				SELECT LabelValue
				FROM LabelTable
				WHERE UPPER(LabelName) LIKE '#DELETE#%'
				)
		);

-- Delete Person from PersonTable if in the Group
DELETE
FROM PersonTable
WHERE PersonID IN (
		SELECT PersonID
		FROM PersonTable
			,GroupTable
		WHERE PersonID BETWEEN StartID
				AND EndID
			AND GroupID IN (
				SELECT LabelValue
				FROM LabelTable
				WHERE UPPER(LabelName) LIKE '#DELETE#%'
				)
		);