-- 'Show Personal 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 PersonalCitations
;
CREATE TEMP VIEW PersonalCitations
AS 
SELECT C.CitationID, C.OwnerType, C.OwnerID as PersonID, Given, Surname, Suffix, C.SourceID, S.TemplateID, S.Name AS SourceName
FROM CitationTable C
INNER JOIN SourceTable S ON S.SourceID = C.SourceID
INNER JOIN NameTable N ON C.OwnerID = N.OwnerID
WHERE C.OwnerType = 0
ORDER BY 
Surname ASC,
Given ASC,
Suffix DESC
;