/*

_color_code_relatives_v2_b.sql

This is step #2 of a four step procedure to color code relatives and to
do so without relying on the new version of the Set Relationips tool
in RM 10.0.1 which sets relationships to spouses of relatives. On its
face, that's a good idea. But many times a person is identifed as
spouse of a relative rather than being identifying an actual relative.
The purpose of this new color coding script is to color code a person
as an actual relative rather than as the spouse of a relative whenever
possible.

This step color codes as red all the ancestors of one base person who
has been color coded red. It actually works just fine if you have defined
more than one base person, such as yourself and your spouse. It color
codes as red the ancestors of all the base people. I'm not set up for
recursive SQLite scripts, so this script must be run a number of times.
That's because it only color codes as red the parents of those people
who are already color coded as red. The first time it is run, it will
color code 2 people. The next time it is run it will color code 4 people.
The time after that it will color code 8 people. The number of people
will continue doubling each time it runs until it runs out of people to
color code. I use SQLiteSpy which uses F9 to execute a script. So a few
quick repetitions of F9 are all that's really required to color code all
the ancestors of the base person. You can tell when to quit repeating
F9 when no more people are updated.

Everything in this script can actually be accomplished directly from the
RM user interface. But when running the overall four steps, it's more
convenient just to run this step from SQLite than from RM.

*/

----------------------------------------------------------------------

UPDATE PersonTable         -- set parents of relatives as relatives
SET Color5 = 1             -- red as the relative color
WHERE Color5 != 1          -- not already a relative
  AND
PersonID IN                -- parent of a relative
(
SELECT DISTINCT F.FatherID
FROM FamilyTable AS F
JOIN ChildTable AS CH ON CH.FamilyID = F.FamilyID
JOIN PersonTable AS P ON P.PersonID = CH.ChildID AND P.Color5 = 1

UNION

SELECT DISTINCT F.MotherID
FROM FamilyTable AS F
JOIN ChildTable AS CH ON CH.FamilyID = F.FamilyID
JOIN PersonTable AS P ON P.PersonID = CH.ChildID AND P.Color5 = 1
)

----------------------------------------------------------------------