Cousin Marriages

#1 · 2025-07-14, 2:55 pm

Has anyone by chance developed a script to report Cousin marriages?

I have seen this in other software. I was going try and write one, but was looking for some ideas -- basically it would be any marriage where both (couple) share at least one common ancestor (Maybe excluding 2nd marriages )

Kevin

#2 · 2025-07-14, 4:41 pm

Quote from thejerrybryan on 2025-07-14, 4:41 pm
This is just sort of thinking out loud. This project would definitely need to use one or more recursive CTE's. With that in mind, I think I would approach it as a series of nested sub-queries as follows. Working from innermost to outermost, I think the queries would be something like the following.
Query #1. Make a list of PersonID's of everybody in your database who has no parents. This would be people who do not appear as a ChildID in the FamilyTable. This would not need to be a recursive CTE. Call that one column in the query the AncestorID.
Query #2. Make a list of AncestorID's and DescendantID's. This would be a two column query. The AncestorID's would be from Query #1. The DescendantID's would be the PersonID's of all direct descendants. AncestorID's with no DescendantID's would drop out of the query. This would have to be a recursive CTE.
Query #3 and Query #4 would be identical except that one would do a JOIN between DescendantID in Query#2 and FatherID in the FamilyTable and the other would do a JOIN between DescendantID in Query#2 and MotherID in the FamilyTable. Query #3 and Query #4 would both retain the AncestorID. These queries would not need to be recursive CTE's.
Finally, Query #5 would do a JOIN between Query #3 and Query #4 on the FamilyID. That would identify all the cousin marriages. This query would not need to be a recursive CTE.
I think a little more thought needs to go into one issue. Namely, the way I have described it, two people would be cousins who married if, for example, they were descended from the same fourth great grandfather and they would cousins who married a second time if, for example, from the same fourth great grandmother, even though the fourth great grandfather and the fourth great grandmother were spouses. So Query #1 surely needs to take FamilyID into account.
I may be missing something obvious, but I think that's about it. All the queries should extremely easy except for Query #2 which would have to be a recursive CTE.

This is just sort of thinking out loud. This project would definitely need to use one or more recursive CTE's. With that in mind, I think I would approach it as a series of nested sub-queries as follows. Working from innermost to outermost, I think the queries would be something like the following.

Query #1. Make a list of PersonID's of everybody in your database who has no parents. This would be people who do not appear as a ChildID in the FamilyTable. This would not need to be a recursive CTE. Call that one column in the query the AncestorID.

Query #2. Make a list of AncestorID's and DescendantID's. This would be a two column query. The AncestorID's would be from Query #1. The DescendantID's would be the PersonID's of all direct descendants. AncestorID's with no DescendantID's would drop out of the query. This would have to be a recursive CTE.

Query #3 and Query #4 would be identical except that one would do a JOIN between DescendantID in Query#2 and FatherID in the FamilyTable and the other would do a JOIN between DescendantID in Query#2 and MotherID in the FamilyTable. Query #3 and Query #4 would both retain the AncestorID. These queries would not need to be recursive CTE's.

Finally, Query #5 would do a JOIN between Query #3 and Query #4 on the FamilyID. That would identify all the cousin marriages. This query would not need to be a recursive CTE.

I think a little more thought needs to go into one issue. Namely, the way I have described it, two people would be cousins who married if, for example, they were descended from the same fourth great grandfather and they would cousins who married a second time if, for example, from the same fourth great grandmother, even though the fourth great grandfather and the fourth great grandmother were spouses. So Query #1 surely needs to take FamilyID into account.

I may be missing something obvious, but I think that's about it. All the queries should extremely easy except for Query #2 which would have to be a recursive CTE.

#3 · 2025-07-14, 5:05 pm

I posed this question to Gemini Flash 2.5 and got what appears to be a comprehensive solution which might be a good solution for you to build on. I did not factor in any exclusion for 2nd Marriage - that would indeed complicate the procedure which is already more complex than I would ever think up! You can see the dialog and code at https://g.co/gemini/share/ad5e204a68e9. Here's a screenshot of some results:

Uploaded files:

kevync has reacted to this post.

#4 · 2025-07-14, 7:59 pm

wow !! that is really slick

Certainly, I can work with thank you so much --- AI is getting scary

#5 · 2025-07-14, 8:02 pm

Thanks Jerry

What you describe below was one of the challenges I was trying figure out -- who was in both sets or neither etc).

think a little more thought needs to go into one issue. Namely, the way I have described it, two people would be cousins who married if, for example, they were descended from the same fourth great grandfather and they would cousins who married a second time if, for example, from the same fourth great grandmother, even though the fourth great grandfather and the fourth great grandmother were spouses. So Query #1 surely needs to take FamilyID into account.

#6 · 2025-07-17, 4:32 pm

I've gone through a number of iterations of the query with Gemini and have arrived at the attached script which is

some 30x faster than the original I reported,
more succinct by listing only the relationship via the Most Recent Common Ancestor(s)
more informative by including:
- names of both MRCA's when they are the family from which the cousin couple is descended
- half-cousin relationships
- number of children for each cousin couple

Here's a sample output:

FatherName	MotherName	CommonAncestor	RelationshipDescription	NumberOfChildren	FatherGenerationsBack	MotherGenerationsBack	total_generations_back
Wyatt Wxxxx-1476	Sarah M. A. Exxxx-1472	Joseph Bxxxxxx-911=Sallie Jxxxx-912	Full First Cousins	0	2	2	4
David Dxxxxx Bxxxx-6512	Mary Bxxxx-440	David Bxxxx-62	Half First Cousins	0	2	2	4
Abraham Pxxxxx-25237	Dixie Axxxxx-27989	Archibald Pxxxxx-5955=Malinda Axxxxx-5951	Full 1th Cousin 1 time(s) removed	7	2	3	5

On a RM10 database having ca 17,000 couples in its FamilyTable, the query returned 294 results in under 9 seconds. The query should work on all versions of RM databases from RM4 to RM10 but has been tested only with RM10 databases.

Uploaded files:

CoupleCousins-09.sql

kevync has reacted to this post.

#7 · 2025-07-17, 7:44 pm

Wow! that is great! I assume you have a pro account to do that?

Thank you so much -- might take me a few mins to digest what all of the code is going -- but this should definitely help me and hopefully it helps others.

#8 · 2025-07-17, 9:26 pm

I used a free account on Google Gemini 2.5 Flash. A few months back, I tried to develop a SQLite3 equivalent to RM Set Relationships with the help of free DeepSeek. We succeeded as far as the basic relationships but bombed out trying to cover half relatives. I may give it another try with Gemini.

kevync has reacted to this post.

#9 · 2025-07-18, 6:48 am

Yeah--- that was on my to-do list -- also some immediate "extended" relationship -- siblings and parents of non blood spouse (of cousin /aunt/ uncle)

#10 · 2025-07-18, 12:03 pm

Tom's query seems to run perfectly on my old fashioned and simple minded SQLiteSpy. The output is not quite as pretty as Tom's, but the output all seems to be correct.

It took a little better than 4 seconds to return 127 rows on my database of about 40,000 people.

kevync has reacted to this post.