/*
_pvt_pri_proof_status.sql       09/26/2024    Jerry Bryan

Identifies all facts for which IsPrimary, IsPrivate, Proof, or
Status is not 0. The query works for both individual facts
and family facts. For individual facts, it displays the RIN.
For family facts, it displays the FatherID and MotherID.

*/

SELECT FT.Name AS FT_name,
       CASE WHEN E.OwnerType = 0 THEN E.OwnerID ELSE ' ' END AS RIN,
       F.FatherID, F.MotherID,
       E.IsPrimary, E.IsPrivate, E.Proof, E.Status       
FROM EventTable AS E
JOIN FactTypeTable AS FT ON FT.FactTypeID = E.EventType
LEFT JOIN FamilyTable AS F ON F.FamilyID = E.OwnerID AND E.OwnerType = 1
WHERE (E.IsPrimary | E.IsPrivate | E.Proof | E.Status) != 0
ORDER BY FT.Name