Facts+-+Reference+Numbers+for+person,+spouse+and+parents.

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

code 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 code =Building Blocks=

List of RINs and Names
It's easy enough to get every person's RIN and Name: code format="sql" 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; code Sample results: code RIN Name ... 286 CLENDENON, Magdalena 287 HOLDEN, Abigal 288 HOLDEN, Barbery 289 MCDONALD, Samuel Charles ... code

List of RINs and RefNos
What about each Person's RefNo value? code format="sql" 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 ; code which gives: code RIN   RefNo ... 328   Groves340 329   Groves284 329   HLAF139 330 ... code 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: code format="sql" 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        ); code Sample from this table, displayed as tab-delimited: code 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

code 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. code format="sql" 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]; code

Sample output

 * RINS.RIN || Person.[RefNo] || Person.[NAME] || Spouse.[RefNo] || Spouse.[NAME] || Father.[RefNo] || Father.[Name] || Mother.[RefNo] || Mother.Name ||
 * > 917 || HLAF061 || HARTLEY, Florence -917 || HLAF102 || BLAKESTON , Sidney -918 || HLAF039 || HARTLEY , Rev. George -875 || HLAF040 || COWIESON , Annetta Jane -876 ||
 * > 918 || HLAF102 || BLAKESTON, Sidney -918 || HLAF061 || HARTLEY , Florence -917 ||  ||   ||   ||   ||
 * > 919 || HLAF103 || BLAKESTON, Zella -919 ||  ||   || HLAF102 || BLAKESTON , Sidney -918 || HLAF061 || HARTLEY , Florence -917 ||

=Download Final Script= The whole script combining all the queries can be run in one fell swoop. Download it here: