Query to get children and parents

Quote from Mark Whidby on 2024-12-31, 2:38 pmThanks for the discussion; this is informative. For my use case, I must start with either PersonTable or NameTable because I want to return everyone , regardless of whether they have a parent or not.
My second attempt started with NameTable, LEFT JOINing with ChildTable to get FamilyID, then LEFT JOINing with FamilyTable to get FatherID and MotherID. So far, so good. I get ChildID (all), FatherID (if exists), and MotherID (if exists).
However, when I LEFT JOIN FamilyTable with a second instance of NameTable on FatherID = OwnerID to get the father's given/surname, I get the name from the first instance of NameTable, which is the child. Example: a person with ChildTable.OwnerID = 1 has a father with FamilyTable.FatherID = 556. When I LEFT JOIN FamilyTable AS FN on FamilyTable.FatherID = FN.OwnerID, I get the given/surname of OwnerID 1, not 556.
Thanks for the discussion; this is informative. For my use case, I must start with either PersonTable or NameTable because I want to return everyone , regardless of whether they have a parent or not.
My second attempt started with NameTable, LEFT JOINing with ChildTable to get FamilyID, then LEFT JOINing with FamilyTable to get FatherID and MotherID. So far, so good. I get ChildID (all), FatherID (if exists), and MotherID (if exists).
However, when I LEFT JOIN FamilyTable with a second instance of NameTable on FatherID = OwnerID to get the father's given/surname, I get the name from the first instance of NameTable, which is the child. Example: a person with ChildTable.OwnerID = 1 has a father with FamilyTable.FatherID = 556. When I LEFT JOIN FamilyTable AS FN on FamilyTable.FatherID = FN.OwnerID, I get the given/surname of OwnerID 1, not 556.

Quote from thejerrybryan on 2024-12-31, 3:48 pmTry something like the following. The people without parents are listed will null parents. People with multiple sets of parents are listed multiple times, once with each set of parents.
And by the way, I had the devil of the time restricting it to primary names. You may or may not want to restrict it to primary names. Instead, you you may wish to include alternate names. But if I didn't hide the test for IsPrimary = 1 inside a subquery, the query took about 5 minutes instead of a fraction of a second. There is obviously something about the logic of a LEFT JOIN that I don't fully grasp. So I had to get the test for IsPrimary = 1 outside of the logic of the LEFT JOIN's.
WITH PrimaryNames AS
(
SELECT N.*
FROM NameTable AS N
WHERE N.IsPrimary = 1)SELECT P.PersonID,
NP.Given AS P_Given,
NP.Surname AS P_Surname,
NF.Given AS F_Given,
NF.Surname AS F_Surname,
NM.Given AS M_Given,
NM.Surname AS M_Surname
FROM PersonTable AS P
JOIN PrimaryNames AS NP ON NP.OwnerID = P.PersonID
LEFT JOIN ChildTable AS CH ON CH.CHildID = P.PersonID
LEFT JOIN FamilyTable AS F ON F.FamilyID = CH.FamilyID
LEFT JOIN PrimaryNames AS NF ON NF.OwnerID = F.FatherID
LEFT JOIN PrimaryNames AS NM on NM.OwnerID = F.MotherID
Try something like the following. The people without parents are listed will null parents. People with multiple sets of parents are listed multiple times, once with each set of parents.
And by the way, I had the devil of the time restricting it to primary names. You may or may not want to restrict it to primary names. Instead, you you may wish to include alternate names. But if I didn't hide the test for IsPrimary = 1 inside a subquery, the query took about 5 minutes instead of a fraction of a second. There is obviously something about the logic of a LEFT JOIN that I don't fully grasp. So I had to get the test for IsPrimary = 1 outside of the logic of the LEFT JOIN's.
WITH PrimaryNames AS
(
SELECT N.*
FROM NameTable AS N
WHERE N.IsPrimary = 1)
SELECT P.PersonID,
NP.Given AS P_Given,
NP.Surname AS P_Surname,
NF.Given AS F_Given,
NF.Surname AS F_Surname,
NM.Given AS M_Given,
NM.Surname AS M_Surname
FROM PersonTable AS P
JOIN PrimaryNames AS NP ON NP.OwnerID = P.PersonID
LEFT JOIN ChildTable AS CH ON CH.CHildID = P.PersonID
LEFT JOIN FamilyTable AS F ON F.FamilyID = CH.FamilyID
LEFT JOIN PrimaryNames AS NF ON NF.OwnerID = F.FatherID
LEFT JOIN PrimaryNames AS NM on NM.OwnerID = F.MotherID

Quote from Mark Whidby on 2024-12-31, 4:55 pmWell that definitely returns the data I'm looking for so, thank you very much!
But I gotta say, I'm puzzled as to why it works with a CTE but not a regular table join. Your version is functionally identical to mine (at least in T-SQL). I guess chalk it up to yet another SQL dialect variation.
Now I gotta transcode your query into Access SQL so I can use it in my Excel analysis file. Access SQL doesn't support CTEs.
Thanks again!
Well that definitely returns the data I'm looking for so, thank you very much!
But I gotta say, I'm puzzled as to why it works with a CTE but not a regular table join. Your version is functionally identical to mine (at least in T-SQL). I guess chalk it up to yet another SQL dialect variation.
Now I gotta transcode your query into Access SQL so I can use it in my Excel analysis file. Access SQL doesn't support CTEs.
Thanks again!

Quote from kevync on 2024-12-31, 8:48 pmso I can use it in my Excel analysis file. Access SQL doesn't support CTEs.
excel's built in power query does (allow CTE sql lang) -- all you need is an ODBC connector also Mcode of Power query is quite powerful/useful. FYI
Kevin
I was dabbling the other day
-- Script for Birth Info & Notes
-- By Kevin Labore
-- vers 12/30/2024WITH
[ChildParents] AS
(
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
),[PersBirths] AS
(
Select EventID, EventType, OwnerID as PID, PlaceID,
SubStr(Date, 4, 4) as EYear,
SubStr(Date, 8, 2) as EMM,
SubStr(Date, 10, 2) as EDD
FROM EventTable
WHERE OwnerType = 0 -- individual
and EventType = 1 -- Birth Event
),[People] AS
(
Select PersonID as PID,
Sex, Living
FROM PersonTable
),[NameInfo] AS
(
Select BirthYear, DeathYear, PersonID as PID, Surname, Given,
Case
WHEN BirthYear <> 0 THEN
Case
WHEN DeathYear = 0 THEN '(' ||BirthYear|| '-' ||' '|| ')'
WHEN DeathYear <> 0 THEN '(' ||BirthYear|| '-' ||DeathYear || ')'
END
WHEN BirthYear = 0 THEN
Case
WHEN DeathYear = 0 THEN '(' ||' '|| '-' ||' '|| ')'
WHEN DeathYear <> 0 THEN '(' ||' '|| '-' ||DeathYear || ')'
END
END as LifeSpan
FROM NameTable
LEFT Join PersonTable on OwnerID = PersonID
WHERE IsPrimary
),[PlaceInfo] AS
(
SELECT PlaceID, Reverse as PlaceReverse
FROM PlaceTable
WHERE PlaceType = 0
)Select [People].PID, Lifespan, Surname, Given, PlaceReverse, EYear, EMM, EDD, Sex, Living, F_Surname, F_Given, M_Surname, M_Given
FROM People
LEFT JOIN [NameInfo] ON [People].PID = [NameInfo].PID
LEFT JOIN [PersBirths] ON [People].PID = [PersBirths].PID
LEFT JOIN [PlaceInfo] ON [PlaceInfo].PlaceID = [PersBirths].PlaceID
LEFT JOIN [ChildParents] ON ChildID = [PersBirths].PID
so I can use it in my Excel analysis file. Access SQL doesn't support CTEs.
excel's built in power query does (allow CTE sql lang) -- all you need is an ODBC connector also Mcode of Power query is quite powerful/useful. FYI
Kevin
I was dabbling the other day
-- Script for Birth Info & Notes
-- By Kevin Labore
-- vers 12/30/2024WITH
[ChildParents] AS
(
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
),[PersBirths] AS
(
Select EventID, EventType, OwnerID as PID, PlaceID,
SubStr(Date, 4, 4) as EYear,
SubStr(Date, 8, 2) as EMM,
SubStr(Date, 10, 2) as EDD
FROM EventTable
WHERE OwnerType = 0 -- individual
and EventType = 1 -- Birth Event
),[People] AS
(
Select PersonID as PID,
Sex, Living
FROM PersonTable
),[NameInfo] AS
(
Select BirthYear, DeathYear, PersonID as PID, Surname, Given,
Case
WHEN BirthYear <> 0 THEN
Case
WHEN DeathYear = 0 THEN '(' ||BirthYear|| '-' ||' '|| ')'
WHEN DeathYear <> 0 THEN '(' ||BirthYear|| '-' ||DeathYear || ')'
END
WHEN BirthYear = 0 THEN
Case
WHEN DeathYear = 0 THEN '(' ||' '|| '-' ||' '|| ')'
WHEN DeathYear <> 0 THEN '(' ||' '|| '-' ||DeathYear || ')'
END
END as LifeSpan
FROM NameTable
LEFT Join PersonTable on OwnerID = PersonID
WHERE IsPrimary
),[PlaceInfo] AS
(
SELECT PlaceID, Reverse as PlaceReverse
FROM PlaceTable
WHERE PlaceType = 0
)Select [People].PID, Lifespan, Surname, Given, PlaceReverse, EYear, EMM, EDD, Sex, Living, F_Surname, F_Given, M_Surname, M_Given
FROM People
LEFT JOIN [NameInfo] ON [People].PID = [NameInfo].PID
LEFT JOIN [PersBirths] ON [People].PID = [PersBirths].PID
LEFT JOIN [PlaceInfo] ON [PlaceInfo].PlaceID = [PersBirths].PlaceID
LEFT JOIN [ChildParents] ON ChildID = [PersBirths].PID

Quote from thejerrybryan on 2024-12-31, 9:30 pmQuote from Mark Whidby on 2024-12-31, 4:55 pmBut I gotta say, I'm puzzled as to why it works with a CTE but not a regular table join. Your version is functionally identical to mine (at least in T-SQL). I guess chalk it up to yet another SQL dialect variation.
Now I gotta transcode your query into Access SQL so I can use it in my Excel analysis file. Access SQL doesn't support CTEs.
A CTE is not really like a regular table join. It's more like a sub-query that's defined out of line of the regular SQL instead of being defined inline with the regular SQL. My limited experience with CTE's so far is that if it works with a CTE, it also works with a sub-query and vice versa.
As far as Access, my experience is that you would make a View that did the same thing as the CTE. You could surely make the CTE into a sub-query in Access, but it would be much more natural in Access to make the CTE into a View. Indeed, it seemed to me that Access wanted to make everything into a View except for the main query itself.
Quote from Mark Whidby on 2024-12-31, 4:55 pmBut I gotta say, I'm puzzled as to why it works with a CTE but not a regular table join. Your version is functionally identical to mine (at least in T-SQL). I guess chalk it up to yet another SQL dialect variation.
Now I gotta transcode your query into Access SQL so I can use it in my Excel analysis file. Access SQL doesn't support CTEs.
A CTE is not really like a regular table join. It's more like a sub-query that's defined out of line of the regular SQL instead of being defined inline with the regular SQL. My limited experience with CTE's so far is that if it works with a CTE, it also works with a sub-query and vice versa.
As far as Access, my experience is that you would make a View that did the same thing as the CTE. You could surely make the CTE into a sub-query in Access, but it would be much more natural in Access to make the CTE into a View. Indeed, it seemed to me that Access wanted to make everything into a View except for the main query itself.

Quote from Tom Holden on 2024-12-31, 9:31 pm@markwhidby, I just tested your SQL query from your reply (post #7 in this discussion) and I get the names of parents from your use of the PersonTable.ParentID. I'm using SQLiteSpy with the fake RMNOCASE (NOCASE relabelled).
What SQLite manager were you using that would not return names? It seems to me you have been struggling to understand what's wrong with your code when it's the interpreter that's at fault.
As to the "getting more records than I have individuals" query in that post, it is to be expected that you will get more records than there are distinct persons in the ChildTable because individuals with no parent record are not recorded in it but they are in the PersonTable and NameTable..
@markwhidby, I just tested your SQL query from your reply (post #7 in this discussion) and I get the names of parents from your use of the PersonTable.ParentID. I'm using SQLiteSpy with the fake RMNOCASE (NOCASE relabelled).
What SQLite manager were you using that would not return names? It seems to me you have been struggling to understand what's wrong with your code when it's the interpreter that's at fault.
As to the "getting more records than I have individuals" query in that post, it is to be expected that you will get more records than there are distinct persons in the ChildTable because individuals with no parent record are not recorded in it but they are in the PersonTable and NameTable..

Quote from Tom Holden on 2024-12-31, 10:00 pmQuote from kevync on 2024-12-31, 8:48 pm
I was dabbling the other day
-- Script for Birth Info & Notes
-- By Kevin Labore
-- vers 12/30/2024That worked well on SQLiteSpy. I'd suggest bringing out the EventTable.IsPrimary and Proof fields as they would be useful to decide what action to take on multiple Birth events for a person.
Running EXPLAIN QUERY PLAN on it exposed me to a couple of new terms I hadn't seen before:
- MATERIALIZE NameInfo (and later ChildParents)
- MULTI-INDEX OR ... INDEX 1... INDEX 2...
- BLOOM FILTER ON NameInfo (PID=?)
MATERIALIZE looks to be associated with CTEs. Maybe also BLOOM FILTER. I haven't used EQP in quite a while so it may be I've just forgotten.
Quote from kevync on 2024-12-31, 8:48 pm
I was dabbling the other day
-- Script for Birth Info & Notes
-- By Kevin Labore
-- vers 12/30/2024
That worked well on SQLiteSpy. I'd suggest bringing out the EventTable.IsPrimary and Proof fields as they would be useful to decide what action to take on multiple Birth events for a person.
Running EXPLAIN QUERY PLAN on it exposed me to a couple of new terms I hadn't seen before:
- MATERIALIZE NameInfo (and later ChildParents)
- MULTI-INDEX OR ... INDEX 1... INDEX 2...
- BLOOM FILTER ON NameInfo (PID=?)
MATERIALIZE looks to be associated with CTEs. Maybe also BLOOM FILTER. I haven't used EQP in quite a while so it may be I've just forgotten.

Quote from kevync on 2025-01-01, 11:17 am@thejerrybryan
A CTE is not really like a regular table join. It's more like a sub-query that's defined
yes -- the way I think about it -- is more like a temp table or permanent view. I started adding too many built in views to RM database so I decided CTE might help minimize or eliminate most of those
A CTE is not really like a regular table join. It's more like a sub-query that's defined
yes -- the way I think about it -- is more like a temp table or permanent view. I started adding too many built in views to RM database so I decided CTE might help minimize or eliminate most of those

Quote from kevync on 2025-01-01, 11:24 am@ve3meo
That worked well on SQLiteSpy. I'd suggest bringing out the EventTable.IsPrimary and Proof fields as they would be useful to decide what action to take on multiple Birth events for a person.
good tip --it was only a draft alpha version so I had not expanded on other things yet. Mainly wanted to test the functionality of being able to pull / assemble info from the different table. I think nearly all of my births have IsPrimary = 0 (which likely is not the same for everyone)
That worked well on SQLiteSpy. I'd suggest bringing out the EventTable.IsPrimary and Proof fields as they would be useful to decide what action to take on multiple Birth events for a person.
good tip --it was only a draft alpha version so I had not expanded on other things yet. Mainly wanted to test the functionality of being able to pull / assemble info from the different table. I think nearly all of my births have IsPrimary = 0 (which likely is not the same for everyone)

Quote from Mark Whidby on 2025-01-02, 10:57 amBack online after a few days. Happy New Year, everyone! And thanks for the continued discussion.
@ve3meo turns out you're right that it was an interpreter issue. I've been using SQLiteStudio. When I tried my query in SQLite Expert, it worked just fine.
@kevync Power Query has been on my list to experiment with. I've used it for decades with different data sources. As you say, it has a better SQL implementation, plus it's much more convenient to have the query right there in Excel rather than having to open up Access to edit queries. I started with Access from a posting on this site to understand the ODBC connector and get familiar with the tables and relationships, but will move to PQ soon. I'm also going to explore your "dabbling" query; looks interesting.
For context to all of this, my goal is to have an Excel sheet listing every individual, birth/death dates and places, any parents and their marriage date and place. Then I can sort and filter on dates, places, names, etc. when I'm trying to link an individual to the right family, etc. My original source data is captured in MS Word in a highly structured format, and I have a script that writes it to the Excel file using the same layout. I've found this to be a very useful tool.
Back online after a few days. Happy New Year, everyone! And thanks for the continued discussion.
@ve3meo turns out you're right that it was an interpreter issue. I've been using SQLiteStudio. When I tried my query in SQLite Expert, it worked just fine.
@kevync Power Query has been on my list to experiment with. I've used it for decades with different data sources. As you say, it has a better SQL implementation, plus it's much more convenient to have the query right there in Excel rather than having to open up Access to edit queries. I started with Access from a posting on this site to understand the ODBC connector and get familiar with the tables and relationships, but will move to PQ soon. I'm also going to explore your "dabbling" query; looks interesting.
For context to all of this, my goal is to have an Excel sheet listing every individual, birth/death dates and places, any parents and their marriage date and place. Then I can sort and filter on dates, places, names, etc. when I'm trying to link an individual to the right family, etc. My original source data is captured in MS Word in a highly structured format, and I have a script that writes it to the Excel file using the same layout. I've found this to be a very useful tool.