-- Events-MismatchedFMNO.sql
-- 2013-10-15 Tom Holden ve3meo
-- 2013-10-16 changed from MRIN to FMNO
/*
Compares the OwnerID for Family events having the Abbrev "FMNO" against
the value stored in the event description, listing those that do not match
along with the names and RINs of the couple.
Used in conjunction with Events-FMNO.sql.
*/
SELECT SUBSTR(CAST(E.[Details] AS TEXT), 6) AS FMNO -- extracts everything after "FMNO "
	,E.[OwnerID] AS FamilyID
	,ifnull(N1.Given || ' ' || UPPER(N1.[Surname]) || '-' || FM.FatherID, '')
   || ' + ' ||
   ifnull(N2.Given || ' ' || UPPER(N2.[Surname]) || '-' || FM.MotherID, '') AS Couple
FROM EventTable E
INNER JOIN FactTypeTable FT ON E.EventType = FT.FactTypeID
INNER JOIN FamilyTable FM ON E.OwnerID = FM.FamilyID
LEFT JOIN NameTable N1 ON FM.FatherID = N1.OwnerID
	AND + N1.IsPrimary
LEFT JOIN NameTable N2 ON FM.MotherID = N2.OwnerID
	AND + N2.IsPrimary
WHERE E.OwnerID NOT LIKE SUBSTR(Details, 6)
	AND E.OwnerType = 1 -- Family event
	AND FT.[Abbrev] LIKE 'FMNO';