Duplicate FSID/RMIDs
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. You can then fix any that may need fixing.
-- 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
Kevin, does this modification of your query give the same results? I don't have a case to test and I'm not really up on CTEs but I wondered if an RMID associated with duplicate FSIDs could be obtained from just the first CTE.
-- Find FSID duplicates by RMID in RM Database
With [DupFSIDs] AS
(
SELECT FSID, RMID, COUNT(RMID)
FROM FamilySearchTable
GROUP BY FSID
Having COUNT(RMID) > 1
)Select RMID as PID, Surname, Given, [DupFSIDs].FSID as FSID
FROM [DupFSIDs]
LEFT JOIN NameTable ON PID = OwnerID
WHERE NameType = 0 and IsPrimary
ORDER BY [DupFSIDs].FSID , Surname, Given
;
I suspect the difference will be that yours lists all the persons having a common FSID while my revision only one of them. Assuming that the resolution has to be in RM, knowing the FSID that's in common should be sufficient to find all of them.
These queries show interesting problems in my database that I may not have found otherwise. Thanks.
Tom, you're right about the results of the two queries, but I find Kevin's to more useful.
Given the FSFTID that is duplicated, how would one search by FSFTID in RM ?
I thought that perhaps by telling RM to display FSFTID instead of RIN, perhaps it would be searchable in the index, but no. (The RIN is still displayed in the index and that may not even be relevant).
Richard Otter
One small point, I have been coding my queries with collate no case clauses to eliminate the need for RMNOCASE.
It may also prevent issues when using unifuzz's RMNOCASE against a database that doesn't have its indexes rebuilt against unifuzz.
What do you folks think about encouraging use of no case vs not?
Quote from Richard Otter on 2024-12-29, 1:00 pmGiven the FSFTID that is duplicated, how would one search by FSFTID in RM ?
FamilySearch ID is a search criterion and an option for customising the People View.
Quote from Richard Otter on 2024-12-29, 1:00 pmThese queries show interesting problems in my database that I may not have found otherwise. Thanks.
Tom, you're right about the results of the two queries, but I find Kevin's to more useful.
Given the FSFTID that is duplicated, how would one search by FSFTID in RM ?
I thought that perhaps by telling RM to display FSFTID instead of RIN, perhaps it would be searchable in the index, but no. (The RIN is still displayed in the index and that may not even be relevant).Richard Otter
Yes you can search in RM by FSID you would need to do manually using OR criteria or have a group. So you could build a group by SQL then reference it that way also.
Quote from Tom Holden on 2024-12-29, 11:30 amKevin, does this modification of your query give the same results? I don't have a case to test and I'm not really up on CTEs but I wondered if an RMID associated with duplicate FSIDs could be obtained from just the first CTE.
I suspect the difference will be that yours lists all the persons having a common FSID while my revision only one of them. Assuming that the resolution has to be in RM, knowing the FSID that's in common should be sufficient to find all of them.
if you do not have any duplicates (which would be a good thing) you would get no result. One database has none and the other (a friends) was where I needed to test and find.
Quote from Richard Otter on 2024-12-29, 2:43 pmWhat do you folks think about encouraging use of no case vs not?
This should be a new topic.