/*
phantom_citation_link_info_9.sql  (RM9 version)
Jerry Bryan    6/18/2024

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

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

UNION

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

UNION

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

UNION

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