/*
_unused_burial_inscription_sources.sql  Jerry Bryan  2024/08/18

I have a custom fact type called Burial Inscription. It includes
a citation to a burial marker, a photo of the burial marker attached
both to the citation and to the fact, and a transcription of the
burial marker. It's intended to be able to be printed in reports or not
by enabling or disabling the fact type for inclusion in reports. In any
case, the citation itsself can often be linked usefully to a Birth fact
or a Death fact or a Burial fact.

This script identifies citations which are attached to the Burial
Inscription fact and which have not been attached Birth, Death,
or Burial. It's sort of quick and dirty in that it only does one
fact type at at time by adjusting code which is and which is not
commented out.

This query will only work in RM8 or later because it uses CitationLinkTable.

*/

--    identify citations which are attached to Burial Inscription facts
SELECT S.Name AS S_name, FT.Name AS FT_name1, B.FT_Name AS FT_name2, E.OwnerID AS RIN
FROM CitationTable AS C
JOIN CitationLinkTable AS CL ON CL.CitationID = C.CitationID AND CL.OwnerType = 2
JOIN SourceTable AS S ON S.SourceID = C.SourceID
JOIN EventTable AS E ON E.EventID = CL.OwnerID
JOIN FactTypeTable AS FT ON FT.FactTypeID = E.EventType AND FT.Name LIKE 'burial inscription'

LEFT JOIN

-- identify citations which are attached to Burial Inscription facts and
-- which are not attached to Birth, Death, or Burial facts (one fact type at a time).
(
SELECT CL.OwnerType, S.Name AS S_name, FT.Name AS FT_name, E.OwnerID
FROM CitationTable AS C
JOIN CitationLinkTable AS CL ON CL.CitationID = C.CitationID AND CL.OwnerType = 2
JOIN SourceTable AS S ON S.SourceID = C.SourceID
JOIN EventTable AS E ON E.EventID = CL.OwnerID
JOIN FactTypeTable AS FT ON FT.FactTypeID = E.EventType AND FT.Name LIKE 'birth'
--JOIN FactTypeTable AS FT ON FT.FactTypeID = E.EventType AND FT.Name LIKE 'death'
--JOIN FactTypeTable AS FT ON FT.FactTypeID = E.EventType AND FT.Name LIKE 'burial'
) AS B ON E.OwnerID = B.OwnerID
ORDER BY B.OwnerID