/*
_bad_set_relationships.sql     7/10/2024      Jerry Bryan

Identifies individuals in an RM10 database who have a missing
"Spouse Of" relationship after running the Set Relationships
tool. The identification of "Spouse Of" reationships such as
"Spouse Of Second Cousin Three Times Removed" is a new feature
in RM10. But there is an apparent bug where the tool misses a
few people. As of yet, no pattern has been identified as to which
people are missed. This script only identifies the people who have
been missed and doesn't fix them.
*/


SELECT F.FatherID, F.MotherID,
    Father.Relate1 AS F_Relate1, Father.Relate2 AS F_Relate2, Father.Flags AS F_Flags,
    Mother.Relate1 AS M_Relate1, Mother.Relate2 AS M_Relate2, Mother.Flags AS M_Flags
FROM FamilyTable AS F
JOIN PersonTable AS Father ON Father.PersonID = F.FatherID
JOIN PersonTable AS Mother ON Mother.PersonID = F.MotherID

WHERE              
( 
   (   (   (F_Relate1 | F_Relate2) != 0)  AND ((M_Relate1 | M_Relate2)  = 0)   )
                                           OR
   (   (   (F_Relate1 | F_Relate2)  = 0)  AND ((M_Relate1 | M_Relate2) != 0)   )
)    
                AND  
                
NOT ( (F_Flags | M_Flags) != 0)