Purpose

This page responds to a request posted in the RootsMagic Forums by Forum member JoyceAE5 on 21 Jan 2014.

Basically, what I want is a list of everyone in my database with the following information:
 
Person's RefNo, Person's Name, Spouse's RefNo, Spouse's Name, Father's RefNo, Father's Name, Mother's RefNo & Mother's Name

Building Blocks

List of RINs and Names

It’s easy enough to get every person’s RIN and Name:

SELECT Per.PersonID AS "RIN"
    ,UPPER(Nam.Surname) || ' ' || Nam.Suffix || ', ' || Nam.[Prefix] || ' ' || Nam.Given AS
    "Name"
FROM PersonTable Per
INNER JOIN NameTable Nam ON Per.PersonID = Nam.OwnerID
    AND +Nam.IsPrimary -- excludes Alternate Names;

Sample results:

RIN Name
...
286 CLENDENON , Magdalena
287 HOLDEN , Abigal
288 HOLDEN , Barbery
289 MCDONALD , Samuel Charles
...

List of RINs and RefNos

What about each Person’s RefNo value?

SELECT Per.PersonID AS "RIN"
    ,CAST(Evt.Details AS TEXT) AS "RefNo"
FROM PersonTable Per
LEFT JOIN EventTable Evt ON Per.PersonID = Evt.[OwnerID]
    AND Evt.EventType = 35 -- the FactTypeID for the Reference Number fact is 35
    AND Evt.OwnerType = 0
    -- restricts to events for individuals; redundant in this case because the FactType is so restricted
    ;

which gives:

RIN    RefNo
...
328    Groves340
329    Groves284
329    HLAF139
330
...

Note that RIN 329 has two RefNo and 330 has none. If many persons have two or more RefNos, the number of combinations reported will go up exponentially; if a person, her spouse and her parents all have two RefNos, that person will be listed 16 times (2^4).

Combine two lists into a lookup table

Since we want the RefNo and Name for each Person, his spouse and parents, it would be most efficient to combine these results in a single lookup table so that the processing need not be repeated for each person and these relatives. That’s achieved by JOINing the two queries on the common field, RIN and storing a query of the results to a temporary table:

DROP TABLE IF EXISTS xNamesRefNoTable;
 
CREATE TEMP TABLE IF NOT EXISTS xNamesRefNoTable AS
    SELECT *
    FROM (
        SELECT Per.PersonID AS RIN
            ,UPPER(Nam.Surname) || ' ' || Nam.Suffix || ', ' || Nam.[Prefix] || ' ' || Nam.
            Given || ' -' || Per.PersonID AS NAME
        FROM PersonTable Per
        INNER JOIN NameTable Nam ON Per.PersonID = Nam.OwnerID
            AND + Nam.IsPrimary -- excludes Alternate Names;
        ) NATURAL
    INNER JOIN (
        SELECT Per.PersonID AS RIN
            ,CAST(Evt.Details AS TEXT) AS RefNo
        FROM PersonTable Per
        LEFT JOIN EventTable Evt ON Per.PersonID = Evt.[OwnerID]
            AND Evt.EventType = 35
            -- the FactTypeID for the Reference Number fact is 35
            AND Evt.OwnerType = 0
            -- restricts to events for individuals; redundant in this case because the FactType is so restricted
        );

Sample from this table, displayed as tab-delimited:

RIN    NAME    RefNo
66    FITCHETT Sr., U.E.,  Joseph -66    Groves073
66    FITCHETT Sr., U.E.,  Joseph -66    HLAF337
67    MCARTHUR ,  Nancy Ann -67    Groves074
68    FITCHETT ,  Dennis -68    Groves075
68    FITCHETT ,  Dennis -68    HLAF361

Note that the RIN has also been appended to the Name for convenience.

Report Query

Now to put together the Person, Spouse and Parents. I’m leaping to a complete report query that uses the temporary table created above, rather than building it up piece-meal. If you examine it from the inside out, you will see how it builds up a query listing the RINs of the person, spouse, father and mother. Then the temporary table is looked up from each RIN to retrieve the corresponding RefNo and Name.

SELECT RINS.RIN
    ,Person.[RefNo]
    ,Person.[NAME]
    ,Spouse.[RefNo]
    ,Spouse.[NAME]
    ,Father.[RefNo]
    ,Father.[Name]
    ,Mother.[RefNo]
    ,Mother.NAME
FROM (
    SELECT Pert.PersonID AS RIN
        ,Spouses.SpouseID
        ,Parents.[FatherID]
        ,Parents.MotherID
    FROM PersonTable Pert
    LEFT JOIN (
        -- Get RIN of Spouse (MotherID)
        SELECT Per.PersonID AS RIN
            ,Fam.[MotherID] AS SpouseID
        FROM PersonTable Per
        INNER JOIN FamilyTable Fam ON Per.PersonID = Fam.[FatherID]
 
        UNION
 
        -- Get RIN of Spouse (FatherID)
        SELECT Per.PersonID AS RIN
            ,Fam.[FatherID] AS SpouseID
        FROM PersonTable Per
        INNER JOIN FamilyTable Fam ON Per.PersonID = Fam.[MotherID]
        ) AS Spouses ON Pert.[PersonID] = Spouses.RIN
    LEFT JOIN (
        -- Get RINs of Parents
        SELECT Per.PersonID AS RIN
            ,Fam.[FatherID]
            ,Fam.[MotherID]
        FROM PersonTable Per
        LEFT JOIN ChildTable Child ON Per.PersonID = Child.[ChildID]
        INNER JOIN FamilyTable Fam USING (FamilyID)
        ) AS Parents ON Pert.[PersonID] = Parents.RIN
    ) AS RINS NATURAL
INNER JOIN xNamesRefNoTable AS Person
LEFT JOIN xNamesRefNoTable AS Spouse ON RINS.SpouseID = Spouse.[RIN]
LEFT JOIN xNamesRefNoTable AS Father ON FatherID = Father.[RIN]
LEFT JOIN xNamesRefNoTable AS Mother ON MotherID = Mother.[RIN];

Sample output

Download Final Script

The whole script combining all the queries can be run in one fell swoop. Download it here: Facts-RefNos_person_spouse_parents.sql