/*
christen_wo_parent_marriage.sql  Jerry Bryan  12/1/2024

Identifies Individuals who have a Christen fact and whose
parents do not have a Marriage fact. There is no further
checking, such as if the individual has a Birth fact in
addition to the Christen fact. So it's quick and simple.
*/


SELECT FT.Name AS FT_Name, E.OwnerID AS Christen_Ownerid, F.FatherID, F.MotherID
FROM EventTable AS E
JOIN FactTypeTable AS FT ON FT.FactTypeID = E.EventType
                        AND FT.Name LIKE 'Christen'
JOIN ChildTable AS CH ON CH.ChildID = E.OwnerID
JOIN FamilyTable AS F ON F.FamilyID = CH.FamilyID
LEFT JOIN
(SELECT E.OwnerID                 -- get family ID's for Marriage facts
FROM EventTable AS E
JOIN FactTypeTable AS FT ON FT.FactTypeID = E.EventType
                        AND FT.Name LIKE 'Marriage'
) AS Marriages ON Marriages.OwnerID = F.FamilyID
WHERE Marriages.OwnerID IS NULL