-- Multi-Spouses.sql
-- new 2010-08-23 by ve3meo
-- Multiple Spouses by person

SELECT * FROM 
(
 SELECT FatherID AS RIN, Surname || ' ' || Suffix || ', ' || Prefix || ' ' || Given AS Name, COUNT(*) AS 'Spouses'
  FROM FamilyTable JOIN NameTable WHERE OwnerID=RIN AND +IsPrimary=1 GROUP BY RIN

 UNION ALL

 SELECT MotherID AS RIN, Surname || ' ' || Suffix || ', ' || Prefix || ' ' || Given AS Name, COUNT(*) AS 'Spouses'
  FROM FamilyTable JOIN NameTable WHERE OwnerID=RIN AND +IsPrimary=1 GROUP BY RIN
)
WHERE  Spouses > 1
ORDER BY Spouses DESC, Name;

-- end