Duplicate Family Search ID

RootsMagic
1

At least once someone asked about identifying / finding duplicate RM to FSID.
When you have more than one RMid to the same FSid. This is not necessarily wrong. But in case you want to identify them – this “simple CTE” Script – will find them – note that you need to access with an SQLITE tool.

-- Find FSID duplicates by RMID  in RM Database
With [DupFSIDs] AS
(
SELECT FSID, COUNT(RMID)
FROM FamilySearchTable
GROUP BY FSID
Having COUNT(RMID) > 1
),

[DupRMIDs] AS
(
SELECT [DupFSIDs].FSID as DupFSID, RMID
FROM [DupFSIDs]
LEFT JOIN FamilySearchTable  ON FamilySearchTable.FSID  = [DupFSIDs].FSID
)


Select RMID as PID, Surname, Given, [DupFSIDs].FSID  as FSID
FROM [DupFSIDs]
LEFT JOIN DupRMIDS ON [DupFSIDs].FSID = [DupRMIDs].DupFSID
LEFT JOIN NameTable ON PID = OwnerID
WHERE NameType = 0 and IsPrimary
ORDER BY  [DupFSIDs].FSID , Surname, Given
2

Interesting use of square brackets in the CTE names.
I think I’ll adopt that.

1 Like
3

:slight_smile: thanks - yes as you noticed I did that to help distinguish Table vs CTE. (might also help with spaces)