-- AllFacts4Person.sql romermb's incredible combination of events, shared events plus 'marriage' and alternate names as Facts 2010-01-06
-- 2010-01-06 rev by ve3meo to bring out RoleName from RoleTableand Sharee's Names from NameTable, tried to speed up using UNION ALL - not much gain - still ~270 secs on a 50,000 event, 16,000 person table.
-- 2010-01-07 rev by romermb to incorporate COUNT, GROUP BY
-- 2010-01-11 rev by ve3meo for 80:1 speedup using index idxNameOwnerID; now ~3sec using SQLiteSpy but won't run on SQLiteman, DBManager et al that use old SQLite releases.
--    runs on SQLiteSpy (fastest) and SQLite Developer (most useful results)
 
-- start with a table of nominally female spouses for the nominally male partners (RIN in FatherID) as a 'fact' for the latter - a Family, even if no actual Marriage
SELECT FactTypeTable.Name COLLATE NOCASE AS Fact, 'Principal' AS 'Role Type', NameTable1.OwnerID AS RIN, NameTable1.Surname COLLATE NOCASE AS Surname, NameTable1.Suffix COLLATE NOCASE AS Suffix, NameTable1.Prefix COLLATE NOCASE AS Prefix, NameTable1.Given COLLATE NOCASE AS 'Given Name', NameTable2.OwnerID AS 'Sharer RIN', NameTable2.Surname COLLATE NOCASE AS 'Sharer Surname', NameTable2.Suffix COLLATE NOCASE AS 'Sharer Suffix', NameTable2.Prefix COLLATE NOCASE AS 'Sharer Prefix', NameTable2.Given COLLATE NOCASE AS 'Sharer Given Name', COUNT(1) AS Count
FROM EventTable
INNER JOIN FactTypeTable ON EventTable.EventType = FactTypeTable.FactTypeID
INNER JOIN FamilyTable ON EventTable.OwnerID = FamilyTable.FamilyID
INNER JOIN NameTable AS NameTable1 indexed by idxNameOwnerID ON FamilyTable.FatherID = NameTable1.OwnerID
INNER JOIN NameTable AS NameTable2 indexed by idxNameOwnerID ON FamilyTable.MotherID = NameTable2.OwnerID
WHERE EventTable.OwnerType = 1 AND NameTable1.IsPrimary = 1 AND NameTable2.IsPrimary = 1
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
 
UNION ALL
-- add nominally male spouses of nominally female partners (RIN in MotherID) as a 'fact' for the latter - a family even if no actual Marriage
SELECT FactTypeTable.Name COLLATE NOCASE, 'Principal', NameTable2.OwnerID, NameTable2.Surname COLLATE NOCASE, NameTable2.Suffix COLLATE NOCASE, NameTable2.Prefix COLLATE NOCASE, NameTable2.Given COLLATE NOCASE, NameTable1.OwnerID, NameTable1.Surname COLLATE NOCASE, NameTable1.Suffix COLLATE NOCASE, NameTable1.Prefix COLLATE NOCASE, NameTable1.Given COLLATE NOCASE, COUNT(1)
FROM EventTable
INNER JOIN FactTypeTable ON EventTable.EventType = FactTypeTable.FactTypeID
INNER JOIN FamilyTable ON EventTable.OwnerID = FamilyTable.FamilyID
INNER JOIN NameTable AS NameTable1 indexed by idxNameOwnerID ON FamilyTable.FatherID = NameTable1.OwnerID
INNER JOIN NameTable AS NameTable2 indexed by idxNameOwnerID ON FamilyTable.MotherID = NameTable2.OwnerID
WHERE EventTable.OwnerType = 1 AND NameTable1.IsPrimary = 1 AND NameTable2.IsPrimary = 1
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
 
UNION ALL
-- add all events for Individual
SELECT FactTypeTable.Name COLLATE NOCASE, 'Principal', NameTable.OwnerID, NameTable.Surname COLLATE NOCASE, NameTable.Suffix COLLATE NOCASE, NameTable.Prefix COLLATE NOCASE, NameTable.Given COLLATE NOCASE, NULL, NULL, NULL, NULL, NULL, COUNT(1)
FROM EventTable
INNER JOIN FactTypeTable ON EventTable.EventType = FactTypeTable.FactTypeID
INNER JOIN NameTable ON EventTable.OwnerID = NameTable.OwnerID
WHERE EventTable.OwnerType = 0 AND NameTable.IsPrimary = 1
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
 
UNION ALL
-- add Alternate Name as a 'Fact'
SELECT 'Alternate name', 'Principal', OwnerID, Surname COLLATE NOCASE, Suffix COLLATE NOCASE, Prefix COLLATE NOCASE, Given COLLATE NOCASE, NULL, NULL, NULL, NULL, NULL, COUNT(1)
FROM NameTable
WHERE IsPrimary = 0
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
 
UNION ALL
-- add shared events other than family as 'Facts' - revised by ve3meo to bring out RoleName from RoleTableand Sharee's Names from NameTable
SELECT FactTypeTable.Name COLLATE NOCASE, RoleName COLLATE NOCASE, NameTable1.OwnerID, NameTable1.Surname COLLATE NOCASE, NameTable1.Suffix COLLATE NOCASE, NameTable1.Prefix COLLATE NOCASE, NameTable1.Given COLLATE NOCASE, EventTable.OwnerID, NameTable2.Surname COLLATE NOCASE, NameTable2.Suffix COLLATE NOCASE, NameTable2.Prefix COLLATE NOCASE, NameTable2.Given COLLATE NOCASE, COUNT(1)
FROM WitnessTable
INNER JOIN roletable ON Role = RoleID
INNER JOIN EventTable ON WitnessTable.EventID = EventTable.EventID
INNER JOIN NameTable AS NameTable1 indexed by idxNameOwnerID ON WitnessTable.PersonID = NameTable1.OwnerID
INNER JOIN NameTable AS NameTable2 indexed by idxNameOwnerID ON EventTable.OwnerID = NameTable2.OwnerID
INNER JOIN FactTypeTable ON EventTable.EventType = FactTypeTable.FactTypeID
WHERE NameTable1.IsPrimary = 1
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
 
ORDER BY RIN;