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
| 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: Facts-RefNos_person_spouse_parents.sql
ve3meo
07 May 2017 20:43:02
Select any cell. Ctrl-C copies the entire row in which the cell is located
Select any cell. Ctrl-A, Ctrl-C copies all rows
You are probably misled by the highlighting – I was,too, initially.
Tom