-- TraceHeadlessWitnesses.sql
-- 2011-12-07 ve3meo
-- List of Headless Witnesses of events and their Principals 

SELECT PersonID AS LostRIN, 
       'shared as', 
       RoleName, 
       FactTypeTable.Name AS Event, 
       'of',
       CASE EventTable.OwnerType
        WHEN 0 THEN NameTable.Surname || ', ' || NameTable.Given || '-' || NameTable.OwnerID 
        WHEN 1 THEN Husb.Surname || ', ' || Husb.Given || '-' || Husb.OwnerID
       END
        AS Principal 
FROM WitnessTable 
LEFT JOIN EventTable USING (EventID) 
LEFT JOIN FactTypeTable ON EventTable.EventType=FactTypeID
LEFT JOIN RoleTable ON Role=RoleID
LEFT JOIN NameTable USING (OwnerID)
LEFT JOIN FamilyTable ON EventTable.OwnerID = FamilyTable.FamilyID
LEFT JOIN NameTable AS Husb ON FamilyTable.FatherID = NameTable.OwnerID
WHERE PersonID IN
(
SELECT PersonID FROM WitnessTable WHERE WitnessTable.PersonID > 0
EXCEPT SELECT PersonID FROM PersonTable 
)
AND EventTable.OwnerType = 0
AND +NameTable.IsPrimary
;