-- SpouseID_Invalid-Check.sql
/*
2017-02-20 Tom Holden ve3meo
rev 2017-02-21 corrected misinterpretation of PersonTable.SpouseID; it is actually the value of the FamilyID

Identify persons with invalid SpouseID in PersonTable.
Can give rise to strange behaviour such as empty Family View.
*/
DROP VIEW IF EXISTS vHasValidSpouseID
;

CREATE TEMP VIEW vHasValidSpouseID AS 
SELECT P.PersonID, F.FamilyID 
FROM PersonTable P
JOIN FamilyTable F
WHERE 
    (P.PersonID = F.FatherID
     AND P.SpouseID = F.FamilyID
     )
 OR (P.PersonID = F.MotherID
     AND P.SpouseID = F.FamilyID
     )
;

SELECT DISTINCT PersonID, SpouseID AS FamilyID
FROM PersonTable
WHERE PersonID NOT IN
(SELECT PersonID FROM vHasValidSpouseID)
AND SpouseID <> 0
;