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

Deletes citation links in CitationLinkTable which
are phantom.
*/

DELETE FROM CitationLinkTable
WHERE LinkID IN
(
SELECT CL.LinkID
FROM CitationLinkTable AS CL 
LEFT JOIN PersonTable AS P ON P.PersonID = CL.OwnerID
WHERE CL.OwnerType = 0 AND P.PersonID IS NULL

UNION

SELECT CL.LinkID
FROM CitationLinkTable AS CL 
LEFT JOIN FamilyTable AS F ON F.FamilyID = CL.OwnerID 
WHERE CL.OwnerType = 1 AND F.FamilyID IS NULL

UNION

SELECT CL.LinkID
FROM CitationLinkTable AS CL
LEFT JOIN EventTable AS E ON E.EventID = CL.OwnerID
WHERE CL.OwnerType = 2 AND E.EventID IS NULL

UNION

SELECT CL.LinkID
FROM CitationLinkTable AS CL
LEFT JOIN NameTable AS N ON N.NameID = CL.OwnerID
WHERE CL.OwnerType = 7 AND N.NameID IS NULL
)