I am trying to find duplicate spousess.
I created the following query:
select fatherid,
(select n1.surname || “, ” || n1.given from nametable n1 where fatherid = ownerid) as fathername,
motherid,
(select n2.surname || “, ” || n2.given from nametable n2 where motherid = ownerid) as mothername
from familytable
order by fatherid, motherid;
1. Should there be blank fatherid’s for a motherid in the result?
2. Or likewise blank motherids for a fatherid in the results?
3. Am I safe to think that if there are 2 motherid’s for the same fatherid that it is a duplicate?
Sorry, I missed this post and not sure why it is in the Blog but that’s a WordPress setup thingie in which I’m not well-versed.
To answer your questions, first-off, on inspection, I’m not sure what this query should give you or if it gives you what you need.
1. The “, ” should be ‘, ‘ or you get an error.
2. From memory, the fatherid and motherid in the FamilyTable should never be blank – zero, yes. And a 0 PersonId will have a blank name. So I’m surprised you get blank ID’s.
3. Two MotherId’s for a given FatherId is not necessarily a duplicate; could be a second wife. Or one pair could be the parents (wife unknown) of a child and another could be the result of a marriage fact. A given FatherId could have any number of pairings with known and unknown spouses.
4. Your query might be delivering an Alternate Name, not the Primary Name for some persons having Alt Names. Add to the WHERE clauses “AND n1.IsPrimary” to get the Primary Name (n2 for the second WHERE).
5. For a large database, this is a long list that’s hard to inspect.
Have you tried using RM’s own filter system in Explorer|Groups using the criteria “number of spouses .. is greater than .. 1”? That, too maye be too broad a result set.
Is what you are after simply duplicate pairings of FatherID, MotherID? For that, the core query could be:
SELECT FatherID, MotherID, COUNT()
FROM FamilyTable
GROUP BY FatherID, MotherID
ORDER BY COUNT() DESC;
I was trying to find a husband who had 2 wives and the wives were the same person. Likewise with the wife who had the same husbands. I merged a bunch of people and I think that is where it happened. Your query will get me started. Ty
I also am trying to create a query that will find more than one parent for a child.