-- Events-sans_media_(res&census).sql
/* 2017-11-29 Tom Holden ve3meo
Lists Census and Residence events lacking a media tag.
Requires RM7_5_WaymarksView.sql to be run first.
*/

SELECT
 EW.Waymarks AS Person
 , FT.Abbrev AS Event
 , SUBSTR(EW.Date,4,4) AS Year 
 , PT1.Name AS Place
 , PT2.Name AS PlaceDetail
 FROM EventWay EW 
 JOIN FactTypeTable FT ON EW.EventType = FT.FactTypeID
 LEFT JOIN PlaceTable PT1 USING(PlaceID)
 LEFT JOIN PlaceTable PT2 ON EW.SiteID = PT2.PlaceID
 WHERE 
  (EW.EventType = 29 -- Residence (Ind)
   OR
   EW.EventType = 310 -- Residence (Fam)
   OR
   EW.EventType = 18 -- Census (Ind)
   OR
   EW.EventType = 311 -- Census (Fam)
   )
 AND EW.EventID NOT IN
  (SELECT DISTINCT OwnerID 
   FROM MediaLinkTable 
   WHERE OwnerType=2) -- list of MediaTags for events
; -- end of script