Quote from Tom Holden on 2024-12-28, 8:30 am

If you are getting only the primary names for the Father and the Person using their IsPrimary = True constraint, then this suggestion is inappropriate:

• change the Mother.IsPrimary = True constraint to simply Mother.IsPrimary. The IsPrimary value is either 0 or 1; no further operation is required to test for false/true. Maybe SQLite does not even recognize True as a symbol.

Otherwise, there must be something amiss in the structure of your query.

Quote from Tom Holden on 2024-12-28, 8:50 am

TRUE and FALSE are not listed in SQLite Keywords. However, my thinking that they might not exist as Booleans in SQLite is out-dated:

Beginning with SQLite 3.23.0 (2018-04-02), SQLite recognizes the identifiers "TRUE" and "FALSE" as boolean literals, if and only if those identifiers are not already used for some other meaning. If there already exists columns or tables or other objects named TRUE or FALSE, then for the sake of backwards compatibility, the TRUE and FALSE identifiers refer to those other objects, not to the boolean values.

The boolean identifiers TRUE and FALSE are usually just aliases for the integer values 1 and 0, respectively. However, if TRUE or FALSE occur on the right-hand side of an IS operator, then the IS operator evaluates the left-hand operand as a boolean value and returns an appropriate answer.

https://www.sqlite.org/lang_expr.html#boolean_expressions

That last sentence might be inferred to say use "IS TRUE" instead of "= TRUE" but neither one is necessary other than for script readability.

Quote from thejerrybryan on 2024-12-28, 10:17 am

I don't have your query to work with, but I'm going to take one of my wild guesses and suggest that your problem may have nothing to do with your test for NameTable.IsPrimary. There are several legitimate ways to make that test. I'm just a simple country boy who often is very literal about such things. So my test typically would be for NameTable.IsPrimary = 1. But as I already said, I suspect that test is not your problem.

Here is the way, I would approach your query. First, I would write it as follows.

SELECT CH.ChildID, CH.FamilyID, F.FatheriD, F.MotherID
FROM ChildTable AS CH
JOIN FamilyTable AS F ON F.FamilyID = CH.FamilyID

This is plain and simple, and it just works. But it only returns the RIN for the child, the father, and the mother. And surely you also wish to display also the names for all three individuals. This basically calls for three JOIN's with NameTable, one to get the child's name, one to get the father's name, and one to get the mother's name. The problem is that the father might missing from FamilyTable or the mother might be missing from FamilyTable. This suggests that the JOIN's to NameTable need to be LEFT JOIN's. But as a sample size of one, I find it devilishly tricky to set up the LEFT JOIN's in this case so that things work correctly no matter if it's the father who is missing or if it's the mother who is missing.

I know I have solved this problem before, but I can't remember the solution. I have to leave soon for a family holiday event, so I don't have time to sort through it until later today. But I suspect that nesting a LEFT JOIN for the father and another LEFT JOIN for the mother is the source of the problem, not the test for NameTable.IsPrimary.

I would approach the problem first of all by simply trying to list the father's name and the mother's name from FamilyTable, so that it would work even when the father is missing or when the mother is missing. This query would have to join the NameTable separately for the father and for the mother. This query would list the father's name even when the mother is missing and vice versa.

Then I would take that query and JOIN it with ChildTable joined with NameTable to pick up the child's name.

Quote from thejerrybryan on 2024-12-28, 1:58 pm

I think this will do what you need. (edited to fix a bug  15:09 eastern time, 28 Dec 2024)

SELECT C.ChildID, C.CH_Surname, C.CH_Given,
A.FatherID, A.F_Surname, A.F_Given,
B.MotherID, B.M_Surname, B.M_Given--, B.M_IsPrimary, COUNT(*)
FROM
(
SELECT F.FamilyID, F.FatherID, N.Surname AS F_Surname, N.Given AS F_Given
FROM FamilyTable AS F
LEFT JOIN NameTable AS N ON N.OwnerID = F.FatherID
WHERE N.IsPrimary = 1 OR N.IsPrimary IS NULL
) AS A
JOIN
(
SELECT F.FamilyID, F.MotherID, N.Surname AS M_Surname, N.Given AS M_Given
FROM FamilyTable AS F
LEFT JOIN NameTable AS N ON N.OwnerID = F.MotherID
WHERE N.IsPrimary = 1 OR N.IsPrimary IS NULL
) AS B ON B.FamilyID = A.FamilyID
JOIN
(
SELECT CH.FamilyID, CH.ChildID, N.Surname AS CH_Surname, N.Given AS CH_Given
FROM ChildTable AS CH
JOIN NameTable AS N ON N.OwnerID = CH.ChildID
WHERE N.IsPrimary = 1 OR N.IsPrimary IS NULL
) AS C ON C.FamilyID = A.FamilyID
ORDER BY C.CH_Surname, C.CH_Given,
A.F_Surname, A.F_Given,
B.M_Surname, B.M_Given

Quote from Tom Holden on 2024-12-28, 8:59 pm

Quote from Mark Whidby on 2024-12-28, 12:01 am

I'm trying to create a query that returns all individuals, and any father and/or mother they have.

Here's a simple looking query that builds from the Wayfinding Views posted at Search - wayfinding from data tables to RootsMagic screens.

SELECT N.Person, FM.Waymarks AS Parents
FROM NameWay N
LEFT JOIN ChildTable CH
ON N.OwnerID = CH.ChildID --AND N.IsPrimary
LEFT JOIN FamilyWay FM
USING(FamilyID)
WHERE N.IsPrimary
;

/* EDITed to correct the location of the constraint to exclude Alt Names which originally allowed parentless person's alternate names to be included. */

Heavy lifting is done in the Waymarks queries which makes the core query fairly simple.

Here's a sample of the result:

Person	Parents
Daniel Cudworth-551	Abraham Cudworth-516 & _____
Mary [Cudworth]-552
Alice Cudworth-553	Daniel Cudworth-551 & Mary [Cudworth]-552

Daniel's mother is unknown.
Mary's surname is unknown and no parents recorded.
Both of Alice's are recorded.

Quote from Mark Whidby on 2024-12-30, 6:51 pm

Thanks, Tom. I'll have to spend some time with the Waymarks stuff.

A larger question for me is, how do I decide when to "anchor" a given query around PersonTable, NameTable, or ChildTable? In this instance, I started with PersonTable and LEFT JOIN'd to FamilyTable twice to get Fathers and Mothers if they exist.

SELECT
P.PersonID
,I.Surname, I.Given
,F.FatherID
,M.MotherID
FROM
PersonTable AS P
INNER JOIN NameTable AS I ON P.PersonID = I.OwnerID and I.IsPrimary
LEFT OUTER JOIN FamilyTable AS F ON P.ParentID = F.FamilyID
LEFT OUTER JOIN FamilyTable AS M ON P.ParentID = M.FamilyID
LEFT OUTER JOIN NameTable AS FN ON F.FatherID = FN.OwnerID and FN.IsPrimary
LEFT OUTER JOIN NameTable AS MN ON M.MotherID = MN.OwnerID and MN.IsPrimary;
;

This gives me what I'm looking for: all individuals, a father ID if there is one, and a mother ID is there is one. Row counts and spot checking confirm the result set is correct.

However, when I add to the SELECT to get Father's name and Mothers' name, I only get the Individual's name:

SELECT
P.PersonID
,I.Surname, I.Given
,F.FatherID
,FN.Surname, FN.Given
,M.MotherID
,MN.Surname, MN.Given
FROM...

Is there something syntactically wrong with my SQL, or with my use of PersonTable rather than ChildTable?

-- EDITED with more details of my confusion below:

On the other hand, if I start with NameTable and left join to ChildTable, I start getting more records than I have individuals:

SELECT
n.OwnerID
,n.Surname
,n.Given
FROM
NameTable n
left join ChildTable c on n.OwnerID = c.ChildID
WHERE
n.isprimary
;

 

 

Quote from Tom Holden on 2024-12-30, 8:08 pm

I think the trap you fell into is that the PersonTable.ParentID (and SpouseID for that matter) are not necessarily the only parent and spouse relationships for that person. They are merely the last ones set to be viewed in the Family View or Summary. Both ParentID and SpouseID contain the FamilyID for the person's last displayed parents and spouse and is intended to speedily re-display that Family View. While that is going to be adequate for persons with a single set of parents and no more than one spouse, it is not the general solution reporting every person and all of their parents (birth, adoptive, step,...) and all of their spouses.

So, to get the full results, you need to start with PersonTable (or NameTable) to get everyone's PersonIDs, left joined to ChildTable (to get all the parent FamilyIDs) which is joined to FamilyTable to get the Father and Mother PersonIDs and retrieve the Names from the NameTable for the Person and their Parents, if any.

If you want to filter out non-Birth parents, you can use ChildTable columns RelFather and RelMother to constrain.

Quote from kevync on 2024-12-30, 9:14 pm

Quote from Tom Holden on 2024-12-30, 8:08 pm

I think the trap you fell into is that the PersonTable.ParentID (and SpouseID for that matter) are not necessarily the only parent and spouse relationships for that person. They are merely the last ones set to be viewed ....

So, to get the full results, you need to start with PersonTable (or NameTable) to get everyone's PersonIDs, left joined to ChildTable (to get all the parent FamilyIDs) which is joined to FamilyTable to get the Father and Mother PersonIDs and retrieve the Names from the NameTable for the Person and their Parents, if any.

If you want to filter out non-Birth parents, you can use ChildTable columns RelFather and RelMother to constrain.

That is  a good call out. Also goes to show one should not make assumptions ....

Quote from thejerrybryan on 2024-12-30, 10:07 pm

I'm working on a project right now that involves getting the names of the person's parents. It functions just like as Tom is describing. I always find it to be trickier than it sounds when I get involved with the FamilyTable and the ChildTable. For example, a child may have more than one set of parents, and a family may have a Father without a Mother or vice versa.

With the project I'm doing right now, I actually don't use the PersonTable  (or not very much). I start with ChildTable.ChildID and that is in effect the person number I need. Under these circumstances, I only join to PersonTable if there is something there I need such as sex or color code. By doing it that way, I have no need to start with PersonTable and then doing a LEFT JOIN to ChildTable to eliminate people without parents. If I start with ChildTable, I automatically eliminate people without parents. But the devil is always in the details. There are lots of times where I do start with the PersonTable.

Then as Tom says, you join the ChildTable and the FamilyTable to get the parents. The join condition is just FamilyTable.FamilyID = ChildTable.FamilyID.

Then if I needed the names of the child and both parents, I would join to NameTable three different times - once on ChildTable.ChildID = NameTable.OwnerID, once on FamilyTable.FatherID = NameTable.OwnerID, and once on FamilyTable.MotherID = NameTable.OwnerID. Unless I wanted or needed the alternate names, I would probably put a restriction of AND NameTable.IsPrimary = 1 on all three joins to the NameTable.