-- 'Show Event Citations.sql'
-- creates EventCitations  
--
-- INTENDED TO CREATE A CONSOLIDATE VIEW OF SOURCE/CITATION/PEOPLE
-- Start with Event type citations from Citation table (OwnerType=2)
-- (excludes Personal, Family, and Alt Name citations)
-- Add person and fact details 

DROP VIEW IF EXISTS EventCitations
;
CREATE TEMP VIEW EventCitations
AS 
SELECT C.CitationID, C.OwnerType, C.OwnerID as EventID, EV.OwnerType as Person0, F.Name As FactType, Given, (N.Surname || '-' || N.OwnerID) AS SurNamewRIN, Suffix, C.SourceID, S.TemplateID, S.Name AS SourceName
FROM CitationTable C
INNER JOIN SourceTable S ON S.SourceID = C.SourceID
INNER JOIN EventTable EV ON C.OwnerID = EV.EventID
INNER JOIN FactTypeTable F ON EV.EventType = F.FactTypeID
INNER JOIN NameTable N ON EV.OwnerID = N.OwnerID
WHERE C.OwnerType = 2
;