Quote from thejerrybryan on 2024-04-21, 8:43 am

I believe there are SQLite managers which support some sort of recursive queries. If you are using such a manager, I think it's possible in one step. I still use the very simple SQliteSpy as my SQLite manager, and it support recursive queries. So I would probably do it in two steps.

Step #1 would just be to create the group in RM itself, with a person and the person's descendants, including spouses. Then I would write an SQLite script to add the parents of the spouses to the group. The query would involve the use or the FamilyTable and the ChildTable - the FamilyTable to identify the spouses and the ChildTable to identify their parents.

That's answering the question in the manner in which it was asked. But being a very lazy programmer and not wishing to deal with some of the nuances of the GroupTable, here is how I would really do it.

Step #1. I would choose an unused color set, and use RM to clear all the colors in that color set. And again using RM, I would color code in that color set (say as red) my initial person and all his or her descendants without spouses.

Step #2. I would write a very simple SQLite script using the FamilyTable to color code in that color set (say as green) all the spouses of  all the red people in that  color set.

Step #3. I would write a very simply SQLite script using the ChildTable to color code (says blue) all the parents of the all the green people in that color set.

Step #4. I would go back into RM and make a group of all the people who were color coded red OR green OR blue in that color set. That let's RM rather than SQLite deal with all the dirty work associated with GroupTable. My SQLite scripts then would not have to do any INSERT operations at all. I would also use RM9's new tools to label the color set as Descendants of John Doe and to label the color red in that color set as Descendants, the color green in that color set as Spouses of Descendants, and the color blue in that color set as Parents of Spouses of Descendants.

Doing it this way makes the SQLite extremely easy and extremely safe. And it makes it possible to deal with the group both as a group using group tools and as color codes using color code tools.

If you like, I can write you the scripts in about five minutes because they are so simple.

Quote from Richard Otter on 2024-04-21, 9:55 am

Take a look at a recent post-

https://sqlitetoolsforrootsmagic.com/forum/topic/groupfromsql-utility-creates-rm-group-from-sql/

 

In a reply to that post, I mention a recursive SQL that first gets the direct line then adds spouses. It should be possible to then add spouses parents. The need for the query is clear. I'm not sure why I didn't add that.

Quote from Jaime Teas on 2024-04-21, 10:14 am

Jerry, if you recall, I don't know how to write scripts.  I took a one-hour online course about a year or two ago and just couldn't begin to make sense of it.  I used to be able to read some MSDOS programming, but it used words/commands that made sense to me.  SQL is a different animal!

Richard Otter provided a script (see his reply below yours) but I have no clue what to do with it to see if it would accomplish my goal.  I would be most thrilled if you could help me to either use Richard's script or write the specific script(s) I need.

Richard, thanks so much for your reply and link!  Hopefully I can get this issue solved.  I have no problem thinking about what I want and knowing there should be a way to do it, but I just don't understand SQL language.

You guys are terrific!!!!!!!!!!!!!

Jaime

 

Quote from thejerrybryan on 2024-04-21, 1:38 pm

Here are your two scripts.

Set your person and descendants without spouses to red using RM.
Run spouse_color_to_green.sql
Run spouse_parent_color_to_blue.sql
Make your group based on the colors as described previously.

I used Color4 which confoundingly enough corresponds in the RM user interface color set 5. I think Bruce misnamed his columns for the color sets. You might very will wish to use a different color set. If so, the column Color is color set 1, column Color1 is color set 2, column Color 2 is color set 3, etc.

It occurred to me after I was finished that you could easily get by with just the spouse_parent_color_to_blue.sql script. Namely, do the following.

Set your person and descendants with spouses to green using RM.
Set your person and descendants without spouses to red using RM. This will leave your descendants red and your spouses green, which is what you want.
Run spouse_parent_color_to_blue.sql
Make your group based on the colors as described previously.

So the only thing you can't do directly from RM is really just the parents of the spouses.

Quote from Jaime Teas on 2024-04-21, 6:41 pm

Jerry, THANK YOU so much.   I'll hopefully be able to try it out soon!

In appreciation ... again,

Jaime

Quote from Jaime Teas on 2024-04-22, 2:16 am

Jerry, I can't get the spouse_parent_color_to_blue.sql to work.

I used colorset 5 and set descendants with spouses to green and then set descendants without spouses to red.

Then I close my database, open SQLite Spy, open my database, open spouse_parent_color_to_blue.sql and close SQL.

Then I open my database, rebuild my indexes, and this is what I see.  Shouldn't the people I highlighted be blue?

I also got a read error when I went to the people view.  I ran an integrity check and all ok afterwards and upon re-opening the database.

Let me know if I am not doing this correctly.

Thanks,

Jaime

Quote from thejerrybryan on 2024-04-22, 10:06 am

Yes, the people you have highlighted should now be blue.

But let's start with your read error. I have no explanation. The field being updated is a numeric field that's not indexed. And there are no INSERT's or DELETE's. So there should be no indexing problems or RMNOCASE problems or anything like that.

It's hard to diagnose the problem from a distance, but if it were on my own computer I would start by running the sub-query as a standalone query.  Namely, the sub-query is the following. What you should be seeing if you run the sub-query is a single column called ParentID and the column should contain the people you highlighted in your screen capture who should be turning blue. So what happens when you run just the sub-query?

SELECT F.FatherID AS ParentID
FROM ChildTable AS C
JOIN PersonTable AS P ON P.PersonID = C.ChildID AND P.Color4 = 9
JOIN FamilyTable AS F ON F.FamilyID = C.FamilyID

UNION

SELECT F.MotherID AS ParentID
FROM ChildTable AS C
JOIN PersonTable AS P ON P.PersonID = C.ChildID AND P.Color4 = 9
JOIN FamilyTable AS F ON F.FamilyID = C.FamilyID

Quote from Tom Holden on 2024-04-22, 11:56 am

Quote from jlodge on 2024-04-22, 2:16 am

Then I close my database, open SQLite Spy, open my database, open spouse_parent_color_to_blue.sql and close SQL.

Because you are so explicit in the steps you've taken, I'm going to suggest that you did not actually Execute the script spouse_parent_color_to_blue.sql. It is not sufficient to simply open the script. That merely opens it in the Editor. You still have to run|execute it.

Quote from Richard Otter on 2024-04-22, 1:52 pm

I sort of promised that I would add another script to handle the request by the OP and I just got it done.

The file name is-
List of cousins with spouses and spouse parents.sql

The direct link is-
https://raw.githubusercontent.com/ricko2001/Genealogy-scripts/main/RM%20-SQL%20for%20creating%20useful%20groups/List%20of%20cousins%20with%20spouses%20and%20spouse%20parents.sql

It is for use with my GroupFromSQL script in that the sql just returns the list of PersonIDs that should go into the group.

Thanks for prompting me to do this. I'll be using it regularly.