/*
phantom_citation_link_info_7.sql  (RM7 version)
Jerry Bryan    6/18/2024

Shows info for citation links in CitationTable which are phantom.
*/

SELECT 'Individual' AS CitationType,
        S.Name AS SourceName,
        C.OwnerID AS RIN1,
        NULL AS RIN2,
        NULL AS FID,
        C.CitationID,
        C.OwnerID
FROM CitationTable AS C
JOIN SourceTable AS S ON S.SourceID = C.SourceID AND C.OwnerType = 0
LEFT JOIN PersonTable AS P ON P.PersonID = C.OwnerID
WHERE P.PersonID IS NULL

UNION

SELECT 'Family' AS CitationType,
       S.Name AS SourceName,
       F.FatherID AS RIN1,
       F.MotherID AS RIN2,
       F.FamilyID AS FID,
       C.CitationID,
       C.OwnerID
FROM CitationTable AS C
JOIN SourceTable AS S ON S.SourceID = C.SourceID AND C.OwnerType = 1
LEFT JOIN FamilyTable AS F ON F.FamilyID = C.OwnerID 
WHERE F.FamilyID IS NULL

UNION

SELECT 'Event' AS CitationType,
       S.Name AS SourceName,
       E.OwnerID AS RIN1,
       NULL AS RIN2,
       NULL AS FID,
       C.CitationID,        
       C.OwnerID
FROM CitationTable AS C
JOIN SourceTable AS S ON S.SourceID = C.SourceID AND C.OwnerType = 2
LEFT JOIN EventTable AS E ON E.EventID = C.OwnerID
WHERE E.EventID IS NULL

UNION

SELECT 'Name' AS CitationType,
       S.Name AS SourceName,
       N.OwnerID AS RIN1,
       NULL AS RIN2,
       NULL AS FID,
       C.CitationID,
       C.OwnerID
FROM CitationTable AS C
JOIN SourceTable AS S ON S.SourceID = C.SourceID AND C.OwnerType = 7
LEFT JOIN NameTable AS N ON N.NameID = C.OwnerID
WHERE N.NameID IS NULL