Quote from Tom Holden on 2025-07-18, 2:04 pm

@thejerrybryan

Thanks for validating results on your database. I know you know your inter-cousin couples very well.

All of my tiny contribution to the query's development and its testing was also done with SQLiteSpy and the sample results were a direct copy of 3 rows from its results pane into the Forum message editor.

I'm almost embarrassed by your calling it "Tom's query" when the real SQL smarts came from Gemini.

Quote from Tom Holden on 2025-07-18, 2:12 pm

Quote from kevync on 2025-07-18, 12:15 pm

I was impressed on how AI was able to take your suggestions and implement revisions... to Tom's credit -- he knew what to ask and how to ask it

 

Did you use the link in the script to access the Gemini chat session? Where did it take you? To the beginning or close to the final iteration?

When I share after a new response, the URL is different from that of a previous response in the same session. However, my browser always seems to go to the start of the chat.

Quote from kevync on 2025-07-18, 2:30 pm

https://g.co/gemini/share/0b73c3f2cff8

Created with 2.5 Flash July 14, 2025 at 03:55 PM Published July 17, 2025 at 04:25 PM

since its flash I guess I cant print it or I would do a PDF

 

sample

You are absolutely correct. My apologies for that oversight! The path_parent_type column was indeed not being carried through the MRCA_Couples CTE when it was used to join back to AllCommonAncestors. That's a classic scope issue in CTEs.

Regarding performance, recursive CTEs can indeed become slow on larger datasets, especially with multiple joins and complex WHERE/GROUP BY clauses within the recursion or subsequent steps. The exponential nature of ancestor tracing can lead to a very large intermediate Ancestors table.

Let's address both the error and the performance concerns.

 

Addressing the "no such column" Error:

 

The error no such column: aca_f.path_parent_type occurs because when MRCA_Couples selects MIN(father_generations_back + mother_generations_back), it aggregates these values. The path_parent_type and path_family_id are not part of the GROUP BY in MRCA_Couples, so they are not carried over. When CousinRelationships then tries to join MRCA_Couples back to AllCommonAncestors on just the IDs and minimum generations, it's not guaranteed to pick the specific path_family_id that corresponds to that MRCA path.

To fix this, we need to ensure that the path_family_id for the specific path that leads to the MRCA is retained.

 

Addressing Performance:

 

Optimizing recursive queries in SQLite is challenging. Here are some strategies we can employ:

1. Reduce Redundancy in Ancestors: The current Ancestors CTE traces all ancestors for all people. We only care about ancestors of FatherID and MotherID in FamilyTable.
2. Indexing: Ensure proper indexes are in place. This is critical for performance.
   • PersonTable.PersonID (already PK)
   • NameTable.OwnerID, NameTable.IsPrimary
   • ChildTable.ChildID, ChildTable.FamilyID, ChildTable.RelFather, ChildTable.RelMother
   • FamilyTable.FamilyID (already PK), FamilyTable.FatherID, FamilyTable.MotherID

Quote from kevync on 2025-07-18, 2:37 pm

I'm almost embarrassed by your calling it "Tom's query" when the real SQL smarts came from Gemini.

well  ... you made an excellent demonstration of using a powerful tool to maximize its full potential .

I tested one and refered to "Rootsmagic database" and its did a good job overall -- except using "repeat" command which failed