-- OrphanedPlaceDetails.sql
-- 2012-02-22 ve3meo
-- Orphaned Place Details are Place Details for which the parent Place 
-- does not exist in the PlaceTable. These may arise because RM5021 does
-- not preclude the entry of such in the Edit Person screen or possibly
-- from its failure to clean up after mergers and deletions. Some are 
-- actually used by Facts/Events and come out in some reports (but not
-- the Place List). Others are no longer accessible from the program.

SELECT PlaceTable.Name AS [Orphaned Place Detail],
 CASE EventTable.OwnerType
 WHEN 0
 THEN
  NameTable.Surname || ', ' ||
  NameTable.Given || ' - ' ||
  NameTable.OwnerID 
 WHEN 1
 THEN
  Husband.Surname || ', ' ||
  Husband.Given || ' - ' ||
  Husband.OwnerID
 END
 AS Person,
 FactTypeTable.Name AS Fact
FROM PlaceTable
LEFT JOIN EventTable
ON PlaceTable.PlaceID = SiteID 
LEFT JOIN FactTypeTable
ON EventType = FactTypeID
LEFT JOIN NameTable
ON EventTable.OwnerID = NameTable.OwnerID AND +NameTable.IsPrimary AND EventTable.OwnerType=0
LEFT JOIN FamilyTable
ON EventTable.OwnerID = FamilyTable.FamilyID AND EventTable.OwnerType=1
LEFT JOIN NameTable AS Husband
ON FamilyTable.FatherID = Husband.OwnerID AND +Husband.IsPrimary
WHERE PlaceTable.PlaceID IN
(
SELECT PlaceID
FROM PlaceTable
WHERE PlaceType = 2
AND
(
 MasterID = 0
 OR
 MasterID NOT IN
 (
  SELECT PlaceID
  FROM PlaceTable
  WHERE PlaceType = 0
 )
)
)
ORDER BY Person
;