/*
find_dup_families.sql    Jerry Bryan     10 Feb 2026

Finds instances of duplicate families an an RM database.
A duplicate family is one where the FatherID and the MotherID
are both the same. It's possible for the FatherID to be zero
or for the MotherID to be zero, but not both at the same time.

In principle, it should be impossible for this situation to arise
because it is prevented in the RM user interface. The one example
I found in my own database goes back to the Family Origins days when
I foolishly was importing a lot of GEDCOM's and merging the results.
It's impossible at this poinit to determine the exact sequence of events
which caused the problem so it can be recreated at will.

There are so many different RM tables where FamilyID is a foreign key
that it seems unwise to write an UPDATE script to fix the problem by
merging the duplicate families together. Rather, I will fix the problem by
unlinking and relinking from within the RM user interface.
*/

WITH DupFamilyTable AS
(
SELECT F.FamilyID, F.FatherID, F.MotherID
FROM FamilyTable AS F
GROUP BY F.FatherID, F.MotherID
HAVING COUNT(*) > 1
)

SELECT F.FamilyID, F.FatherID, F.MotherID, Ch.ChildID
FROM DupFamilyTable AS DupF
JOIN FamilyTable AS F ON F.FatherID = DupF.FatherID AND F.MotherID = DupF.MotherID
LEFT JOIN ChildTable AS Ch ON Ch.FamilyID = F.FamilyID