/*
_color_code_relatives_v2_c.sql

This is step #3 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 script color codes as red all the descendants of the people who
are already color coded as red. It does so one generation at a time
by color coding as red all the people who have a parent who is already
color coded as red. I'm not set up for recursive SQLite scripts, so
this script must be run a number of times. It works its way down one
generation at a time. 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 descendants of of the base person the people who are
already color coded as red. You can tell when to quit repeating
F9 when no more people are updated.

This step can almost but not quite be accomplished from the RM user
interface by using the tool to mark ancestors and then desendants of
the ancestors. The problem with the tool is that also marks spouses
and we do not wish to include spouses. Another option from the RM user
interface would be to mark all descendants of each end of line ancestor
because that tool supports including or not including spouses. But it
could take a long time to go to each end of line ancestor. It's just
faster just to click F9 a bunch of times.

*/

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

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

UNION

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

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