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.

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.

Discussions & comments from Wikispaces site

ve3meo

Comment: “…the need for program recursion for ancestral lines …”

ve3meo
03 September 2018 19:37:05

ve3meo Mar 28, 2014

SQLite 3.8.4.1 and somewhat earlier introduced the WITH clause with the RECURSIVE option which should allow ancestral queries in a tree. The current version of SQLite Expert Personal supports this new clause but SQLite Spy does not.
ve3meo Mar 28, 2014

My first success with recursion in SQLite: Ancestors+Query+-+Recursive