Intro

This query lists all the ancestral lines for a given RIN in your database out to the 13th generation. It is just a table of RINs but could be extended to provide names et al. More significantly, it can be the basis of other queries that need to follow ancestral lines. One of these, already developed is Ancestors Named Group which refreshes the membership of a specially named group in a RM4 database.

AncestorsScreenshot-Spy.PNG
From SQLiteSpy showing the full ancestral lines for three persons plus part of a fourth. Use SQLite Developer or Expert Personal to specify the starting person's RIN when the query is run - absolutely necessary for large databases.

There was earlier discussion about the need for program recursion for ancestral lines in Set Living Flag and in Another version of a Set Living query. This query does not achieve recursion as it seems quite likely that it cannot be accomplished within SQLite; a higher level language making calls to SQLite seems necessary. However, the script appears amenable to programmatic generation so that instead of recursive calls to SQLite, a dynamic script itself could be written to cover the desired number of generations. RootsMagic 4 may do it either way as the number of generations is a required input.

No attempt was made to filter on the type of relationship (blood, adoptive) between child and parent so the results include all kinds of ancestry.

Download

Ancestors.sql

Usage

This query uses a runtime parameter for the entry of the RIN of the person whose ancestral lines are to be generated. If left blank, or if your SQLite manager does not support runtime parameters, it will attempt to generate the ancestral lines for EVERYONE in your database. This will be very sloooow and may crash the program. SQLite Expert Personal and SQLite Developer ($29 version) do work with the runtime parameter; SQLite Spy skips over it.