-- Place Details List with Relevant Facts and Individuals/Families
-- including those who share a Fact with a Principal,
-- with the exception of those whose names were just typed in, but who aren't actually in the database

-- created by romermb on 16 Jan 2010, with an assist from ve3meo
-- 2010-05-31 rev by ve3meo prefixing constraints using IsPrimary field from 
--   NameTable with + to hide it from query optimiser

-- information relating to Individual facts, but only for Principal Role of Individual;
-- left out shared information to shorten result set
SELECT   PlaceTable2.Name COLLATE NOCASE AS Place, PlaceTable1.Name COLLATE NOCASE AS 'Place Details',
         PlaceTable1.Note AS 'Place Details Note', FactTypeTable.Name COLLATE NOCASE AS 'Fact Type',
         'Principal' AS Role1, NULL AS MRIN1, NameTable.OwnerID AS RIN1,
         NameTable.Surname COLLATE NOCASE AS Surname1, NameTable.Suffix COLLATE NOCASE AS Suffix1,
         NameTable.Prefix COLLATE NOCASE AS Prefix1, NameTable.Given COLLATE NOCASE AS Given1,
         NULL AS Role2, NULL AS MRIN2, NULL AS RIN2,
         NULL AS Surname2, NULL AS Suffix2,
         NULL AS Prefix2, NULL AS Given2,
         NULL AS Role3, NULL AS MRIN3, NULL AS RIN3,
         NULL AS Surname3, NULL AS Suffix3,
         NULL AS Prefix3, NULL AS Given3,
         COUNT(1) AS 'Number of Records'
FROM     PlaceTable AS PlaceTable1, PlaceTable AS PlaceTable2,
         EventTable, FactTypeTable,
         NameTable
WHERE    PlaceTable1.MasterID = PlaceTable2.PlaceID AND PlaceTable1.PlaceID = EventTable.SiteID AND
         EventTable.EventType = FactTypeTable.FactTypeID AND
         EventTable.OwnerID = NameTable.OwnerID AND
         EventTable.OwnerType = 0 AND +NameTable.IsPrimary = 1
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25

UNION ALL

-- information relating to Family facts, but only for Principal Role of Family;
-- left out shared information to shorten result set
SELECT   PlaceTable2.Name COLLATE NOCASE, PlaceTable1.Name COLLATE NOCASE,
         PlaceTable1.Note, FactTypeTable.Name COLLATE NOCASE,
         'Principal', EventTable.OwnerID, NameTable1.OwnerID,
         NameTable1.Surname COLLATE NOCASE, NameTable1.Suffix COLLATE NOCASE,
         NameTable1.Prefix COLLATE NOCASE, NameTable1.Given COLLATE NOCASE,
         'Principal', EventTable.OwnerID, NameTable2.OwnerID,
         NameTable2.Surname COLLATE NOCASE, NameTable2.Suffix COLLATE NOCASE,
         NameTable2.Prefix COLLATE NOCASE, NameTable2.Given COLLATE NOCASE,
         NULL, NULL, NULL,
         NULL, NULL,
         NULL, NULL,
         COUNT(1)
FROM     PlaceTable AS PlaceTable1, PlaceTable AS PlaceTable2,
         EventTable, FactTypeTable,
         FamilyTable, NameTable AS NameTable1, NameTable AS NameTable2
WHERE    PlaceTable1.MasterID = PlaceTable2.PlaceID AND PlaceTable1.PlaceID = EventTable.SiteID AND
         EventTable.EventType = FactTypeTable.FactTypeID AND
         EventTable.OwnerID = FamilyTable.FamilyID AND
         FamilyTable.FatherID = NameTable1.OwnerID AND FamilyTable.MotherID = NameTable2.OwnerID AND
         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, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25

UNION ALL

-- information relating to Individual shared facts for Sharer,
-- including Individual Sharee information for reference, but without increased result set
SELECT   PlaceTable2.Name COLLATE NOCASE, PlaceTable1.Name COLLATE NOCASE,
         PlaceTable1.Note, FactTypeTable.Name COLLATE NOCASE,
         RoleTable.RoleName COLLATE NOCASE, NULL, NameTable1.OwnerID,
         NameTable1.Surname COLLATE NOCASE, NameTable1.Suffix COLLATE NOCASE,
         NameTable1.Prefix COLLATE NOCASE, NameTable1.Given COLLATE NOCASE,
         'Principal', NULL, NameTable2.OwnerID,
         NameTable2.Surname COLLATE NOCASE, NameTable2.Suffix COLLATE NOCASE,
         NameTable2.Prefix COLLATE NOCASE, NameTable2.Given COLLATE NOCASE,
         NULL, NULL, NULL,
         NULL, NULL,
         NULL, NULL,
         COUNT(1)
FROM     PlaceTable AS PlaceTable1, PlaceTable AS PlaceTable2,
         EventTable, FactTypeTable,
         WitnessTable, RoleTable,
         NameTable AS NameTable1, NameTable AS NameTable2
WHERE    PlaceTable1.MasterID = PlaceTable2.PlaceID AND PlaceTable1.PlaceID = EventTable.SiteID AND
         EventTable.EventType = FactTypeTable.FactTypeID AND
         EventTable.EventID = WitnessTable.EventID AND
         WitnessTable.Role = RoleTable.RoleID AND
         WitnessTable.PersonID = NameTable1.OwnerID AND EventTable.OwnerID = NameTable2.OwnerID AND
         EventTable.OwnerType = 0 AND +NameTable1.IsPrimary = 1 AND +NameTable2.IsPrimary = 1
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25

UNION ALL

-- information relating to Family shared facts for Sharer,
-- including Family Sharee information for reference, but without increased record set
SELECT   PlaceTable2.Name COLLATE NOCASE, PlaceTable1.Name COLLATE NOCASE,
         PlaceTable1.Note, FactTypeTable.Name COLLATE NOCASE,
         RoleTable.RoleName COLLATE NOCASE, NULL, NameTable1.OwnerID,
         NameTable1.Surname COLLATE NOCASE, NameTable1.Suffix COLLATE NOCASE,
         NameTable1.Prefix COLLATE NOCASE, NameTable1.Given COLLATE NOCASE,
         'Principal', FamilyTable.FamilyID, NameTable2.OwnerID,
         NameTable2.Surname COLLATE NOCASE, NameTable2.Suffix COLLATE NOCASE,
         NameTable2.Prefix COLLATE NOCASE, NameTable2.Given COLLATE NOCASE,
         'Principal', FamilyTable.FamilyID, NameTable3.OwnerID,
         NameTable3.Surname COLLATE NOCASE, NameTable3.Suffix COLLATE NOCASE,
         NameTable3.Prefix COLLATE NOCASE, NameTable3.Given COLLATE NOCASE,
         COUNT(1)
FROM     PlaceTable AS PlaceTable1, PlaceTable AS PlaceTable2,
         EventTable, FactTypeTable,
         WitnessTable, RoleTable,
         NameTable AS NameTable1, FamilyTable, NameTable AS NameTable2, NameTable AS NameTable3
WHERE    PlaceTable1.MasterID = PlaceTable2.PlaceID AND PlaceTable1.PlaceID = EventTable.SiteID AND
         EventTable.EventType = FactTypeTable.FactTypeID AND
         EventTable.EventID = WitnessTable.EventID AND
         WitnessTable.Role = RoleTable.RoleID AND
         WitnessTable.PersonID = NameTable1.OwnerID AND EventTable.OwnerID = FamilyTable.FamilyID AND
         FamilyTable.FatherID = NameTable2.OwnerID AND FamilyTable.MotherID = NameTable3.OwnerID AND
         EventTable.OwnerType = 1 AND +NameTable1.IsPrimary = 1 AND +NameTable2.IsPrimary = 1 AND NameTable3.IsPrimary = 1
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25

ORDER BY 1 COLLATE NOCASE, 2 COLLATE NOCASE