-- FactsHavingPlaceDetails.sql
-- Returns all Places with Place Details and the Persons and their Facts using the Place Detail.
-- #1 2010-01-14 ve3meo
-- #2 2010-01-15 ve3meo added MRIN and ORDERED BY 

-- Start with Individuals 
SELECT  p.name AS Place, p2.name AS 'Place detail', n.ownerid AS RIN, 
  n.surname AS Surname, n.given AS Given, f.name AS Fact, NULL AS MRIN
FROM  placetable p, placetable p2, eventtable e, facttypetable f, nametable n
WHERE  p.placeid=p2.masterid AND p2.placeid=e.siteid AND e.eventtype=f.facttypeid AND 
  e.ownerid=n.ownerid AND e.OwnerType=0  

UNION ALL

-- Add nominally male partner for Marriage fact
SELECT  p.name, p2.name, n.ownerid, 
  n.surname, n.given, f.name, f2.FamilyID
FROM  placetable p, placetable p2, eventtable e, facttypetable f, nametable n , familytable f2
WHERE  p.placeid=p2.masterid AND p2.placeid=e.siteid AND e.eventtype=f.facttypeid AND 
  e.ownerid=f2.familyid and f2.FatherID=n.OwnerID AND e.OwnerType=1  

UNION ALL

-- Add nominally female partner for Marriage fact
SELECT  p.name, p2.name, n.ownerid, 
  n.surname, n.given, f.name, f2.FamilyID
FROM  placetable p, placetable p2, eventtable e, facttypetable f, nametable n , familytable f2
WHERE  p.placeid=p2.masterid AND p2.placeid=e.siteid AND e.eventtype=f.facttypeid AND 
  e.ownerid=f2.familyid and f2.MotherID=n.OwnerID AND e.OwnerType=1   

ORDER BY Place COLLATE NOCASE , "Place Detail" COLLATE NOCASE , MRIN, Fact COLLATE NOCASE ,RIN