-- Place Details List with Relevant Facts and Individuals/Families
-- not including those who share a Fact with a Principal
-- created by romermb on 15 Jan 2010, with an assist from ve3meo
-- 2010-05-31 rev by ve3meo to prefix field IsPrimary from NameTable with +
--   where used as a SELECT constraint to hide it from the query optimiser

-- information relating to Individual facts
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',
         NULL AS MRIN, NameTable.OwnerID AS RIN,
         NameTable.Surname COLLATE NOCASE AS Surname, NameTable.Suffix COLLATE NOCASE AS Suffix,
         NameTable.Prefix COLLATE NOCASE AS Prefix, NameTable.Given COLLATE NOCASE AS Given,
         NULL AS 'Mother RIN',
         NULL AS 'Mother Surname', NULL AS 'Mother Suffix',
         NULL AS 'Mother Prefix', NULL AS 'Mother Given',
         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

UNION ALL

-- information relating to Family facts
SELECT   PlaceTable2.Name COLLATE NOCASE, PlaceTable1.Name COLLATE NOCASE,
         PlaceTable1.Note, FactTypeTable.Name COLLATE NOCASE,
         EventTable.OwnerID, NameTable1.OwnerID,
         NameTable1.Surname COLLATE NOCASE, NameTable1.Suffix COLLATE NOCASE,
         NameTable1.Prefix COLLATE NOCASE, NameTable1.Given COLLATE NOCASE,
         NameTable2.OwnerID,
         NameTable2.Surname COLLATE NOCASE, NameTable2.Suffix COLLATE NOCASE,
         NameTable2.Prefix COLLATE NOCASE, NameTable2.Given COLLATE NOCASE,
         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

ORDER BY 1 COLLATE NOCASE, 2 COLLATE NOCASE