[Solved] Selection and reuse of data in a query

[PurplePix]

In an Open Office Database Query (Embedded Database, HSQL database engine) I am SELECTING from Tbl.FTP, Columns p.FID, p.PFID, p.PMID & p3.CHID

How can I use p3.CHID to replace the value of p.FID so I can get three new values?

I have tried the attached code below and get an unexpected token CASE, requires FROM in statement [SELECT

Is it possible to reuse data within a query, and what is missing from CASE that gives me an error?

Code: Select all

 SELECT
	p."FID",
	p."PFID",
	p."PMID",
	p3."CHID"
	CASE
		WHEN 
			"FTP". p."CHID" <> "FTP". p."FID"
		THEN 
			"FID01" = "CHID"
		ELSE
			"Failed"
	END
FROM 
	"FTP" p
	left outer join ( select "FID", "CHID" From "FTP") p3 on p."PFID" = p."FID"
WHERE
	"FID" = 63 

[eremmel]

A few remarks: You are using table aliases for FTP as p and p3 for your derived table (sub query). That is fine, but you did not use p3 in the join condition. You did also not use p3 in your select clause. You assigned FID01 in the case, but that is not how it works. A CASE just returns a value that acts as another column. With an alias you can 'name' that column. So the following might work.
I added in query comments starting with --, remove those.

Code: Select all

SELECT
	p."FID", p."PFID", p."PMID", p3."CHID",     -- Added , beteen CHILD and CASE.
	CASE
		WHEN 
			p."CHID" <>  p."FID"      --- You need a p3 here on one of the columns?
	 	THEN 
			"CHID"   -- You need to choose p or p3 or do you mean  'CHILD' literal value?
		ELSE
			'Failed'   -- Literal values need to between  ' and  ' 
	END AS "FID01" -- Here you name the column.
FROM 
	"FTP" p
	left outer join ( select "FID", "CHID" From "FTP") p3 on p."PFID" = p."FID"   --- You need a p3 here on one of the fields.
WHERE
	"FID" = 63    -- You need here to choose a p or p3, else column name is ambiguous.  

[PurplePix]

Eremmel thank you for your reply.
Yes I am using aliases because it was in code I got from an earlier request on a different question and with my limited knowledge in SQL allowed me to achieve a solution for that challenge.

You said " A CASE just returns a value that acts as another column." what I really need is someway to reuse that information to replace FID to repeat the query a number of times. FID comes in from a spread sheet Properties Form after a page refresh with a push button, through a Filter table in the database, that is used by a query from a single line in the database. This problem is different, the data is in multiple rows of the database and CHID contains the value of the next FID row or is blank. That data also is dynamically present on a single Calc sheet after entering a new FID in one cell (Calculations extract the correct data from two other sheets and fill specific cells), but I see no way of getting it to the data base or using it to get the images for display from a folder within the same folder as the spreadsheet and database.

When I was putting the request together, I poorly copied the "Left Outer Join" that you corrected for me. I added p3 to the last FID and it produced the result I expected
left outer join ( select "FID", "CHID" From "FTP") p3 on p."PFID" = p."FID"
left outer join ( select "FID", "CHID" From "FTP") p3 on p."PID" = p3."FID"

[UnklDonald418]

what I really need is someway to reuse that information to replace FID to repeat the query a number of times

Interpreted languages like SQL are not recursive, so I believe Villeroy's horse pedigree example is the closest you can come to recursion using an Embedded database.
Later versions of HSQL do have a RECURSIVE WITH function although I've never had occasion to use it.
Those versions of HSQL require a JDBC connection, often referred to here as a Split database where the database engine and the tables are stored outside of the .odb file.
viewtopic.php?t=61183
The above link also has a link to a pdf version of the HSQL User Guide where you can see what is involved in using the RECURSIVE WITH function

[chrisb]

@PurplePix,

i have adapted an existing database in an effort to imitate what i think is close to your objective.
i cannot include the actual images due to this sites data limit.
check out the query qTree, it is the data-source for the form.
a small macro is used to refresh the form when the list box value is changed.
create a folder in the same directory as the database and use it to store your pictures then alter "FilePath" in the table "tPersonPhoto" accordingly.

Purple.odb

(18.32 KiB) Downloaded 255 times

[PurplePix]

UnklDonald418, I spent a lot of time looking at Villeroy's Horse Pedigree, and unless I missed something important. There didn't appear to be a clear identification of the Foals that came from the Stud and the Dam.
One thing I have tried to do is keep the Family Tree simple. It started as a spreadsheet with complex formulas to extract the entered data. I was told in the beginning I should be using a Database or a commercially available program. The pictures in the Family Display sheet, forced me into a database and the extracted data exists in the spreadsheet and also the FamilyTreePhotos table in the database (I understand this is not the best solution as the data could be different in either the spreadsheet or the Database table). Because the data is quite stable and usually entered once this was the best solution, because even then I was having trouble getting SQL to extract the data. Because I am trying to keep it simple, I am leaning towards Chrisb's solution and not ruling anything out.

Chrisb thank you for your suggestion, it will take me some time to work through Purple.odb but it looks like I might be able to make it work. Because you started with a value from the filter table, and take it through SQL, I should be able to work my way through it (it will take me some time, but the hours and hours with SQL and piles of notes) to get it working the way I want. We both do our process in opposite ways, in FTP your FID (FamilyID) in rows that include the Child, Father and Mother which might be the proper way, so for example Father 5 and Mother 4 have three children 6, 13 & 14. I have mine on a single FamilyID row with Father, Mother and up to 20 children (yes we have one with that many, My dad came from a family of 13 children).

Give me some time to creatively work with what you sent, and I will post an update.

Keeping it simple I have stayed away from macro's, how do I see the macro and the information inside of it?

[eremmel]

I have mine on a single FamilyID row with Father, Mother and up to 20 children

Looks like you have a nice family without divorces and remarriages and children of different parent combinations.
But when those sad things happen I expect that your data model is far too simple.

Having the capability for unrolling all ancestors of a person benefits from a database with modern capabilities.

Unfortunate this is not trivial at all, but there are enough professionals here to help you out.

All starts with requirements, choosing the right tools and the correct data model (most of time there is not only one such model).

[PurplePix]

eremmel, my brother started a Family tree that was less than 10 pages, In 2016 after major surgery, I had time on my hands (I am a retired Heavy Duty Mechanic) and with mostly self taught computer skills

decided to try to build a Family tree in Open Office Calc. Our cousin had a commercial Family tree, and through software and computer upgrades lost all his data. I had a chance to see his printed data. With a one

day access to it digitally photographed over 1000 pieces of paper documentation. I also had a book that has the information of early family, and I was able to use that during the building and development stage to

verify that my formulas were giving me the right results. Tha goal was to have Family tree that I could run on my computer.

Initially the data was put in a People sheet that included all the data about a person. As the number grew it was easier to take the marriage information and put that in a Families sheet. These two sheets are the

heart of the family tree. Initially I set it up for 999 people and 750 Families, recently I increased it to 1500 people as we approached 900 people. Knowing that to extract the data for each family or person would

requires many hours of work, so an easier way was needed even if I had to put the time in up front.

First there was the Family Display sheet, that only requires entry of a Family number and [ENTER] to populate the sheet. Everything about Father & Mother, all their children with spouses/partners with up to

four marriages and number of children. Parents also show family numbers for other marriages as well as their parents. Normal marriages, common law and same sex are covered. The page has reference numbers

displayed in gray and drop down lists to look up people or families, the idea was to simplify finding data in the tree.

Other sheets include Your tree - A graphical tree that shows direct line up each side of the tree. Who is - like the Family display page but allows you to look at a person with their ID number, this covers everyone

also unmarried single people and children. Expanded tree allows you to look at everyone in two families for comparison. Lineage shows Father and Mother , their children and parents for each family (31

potential) up four generations. All data retrieval is done with a single family or person number through complex formulas.

Divorces were never considered, unless that information is provided, it is about what can be found or is provided, and expansion is possible.

A Family tree or as we refer to is as the Family Forest, without photos is just data and numbers. I was told by this forum that photos in a spread sheet were impossible. My Family tree and database live inside a

Folder "FAMILY FOREST PHOTO TREE", also in that folder is a Folder "P_Person" where all the photos for the tree are kept. Using Design mode in Calc I built the necessary pieces so I could enter a number

from a dropdown list, press a refresh button and populate image holders with pictures where available, this is working on the Display, Who is and Your tree.

I am currently working to get pictures on the Lineage sheet, the Calc sheet already provided the information to extract the data and works amazingly well. I have the information in a Database table looking for a

simple way to extract it for use in Calc. I am currently looking at Chrisb's Purple Database to see how I can reconfigure it to work exactly the way I want. The first part is to make sure it gives me the data I expect.

Example code to get death and age at death:

Code: Select all

=IF(AND(O29>0;O30>0;P29>0;P30>0;INDIRECT("People.F"&$B$12)>=INDIRECT("People.C"&$B$12))  ;"DOD YYYY-MM-DD  "&INDIRECT("People.E"&$B$12)&"-"&INDIRECT("People.F"&$B$12)&"-"&INDIRECT("People.G"&$B$12)&"         "&" *  Age at Death "&VALUE(INDIRECT("People.E"&B$12)-VALUE(INDIRECT("People.B"&$B$12)))   ;IF(AND(O29>0;O30>0;INDIRECT("People.F"&$B$12)<INDIRECT("People.C"&$B$12))  ;"DOD YYYY-MM-DD  "&(INDIRECT("People.E"&$B$12))&"-"&(INDIRECT("People.F"&$B$12))&"-"&(INDIRECT("People.G"&$B$12))&"         "&" *  Age at Death "&VALUE(INDIRECT("People.E"&B$12)-1)-VALUE(INDIRECT("People.B"&$B$12));  IF(AND(O29>0;O30>0;INDIRECT("People.F"&$B$12)>=INDIRECT("People.C"&$B$12))  ;"DOD YYYY-MM-DD  "&INDIRECT("People.E"&$B$12)&"-"&INDIRECT("People.F"&$B$12)&"-"&INDIRECT("People.G"&$B$12)&"         "&" *  Age at Death "&VALUE(INDIRECT("People.E"&B$12)-VALUE(INDIRECT("People.B"&$B$12)))  ;"DOD YYYY-MM-DD  ")))

[chrisb]

i penned this before reading your post of Fri Apr 14, 2023 5:13 pm. as you wish to stick with your spreadsheet most of it is probably irrelevant.

how do I see the macro

menu: > Tools > Macros > Organise Macros > OpenOfficeBasic
in the left hand pane: expand 'Purple', expand 'Standard' then click on 'Module1'.
click on 'Edit' to view the code.

to get it working the way I want

no one really knows what you have, what you want or the format in which you want it displayed.

descendants and ascendants are linked directly by blood line, family members are not always linked directly by blood line therefore families should be handled separately.
using HSQLDB embedded it's impractical to show descendants due to the possible vast numbers.
on the other hand each descendant has exactly two immediate ascendants (mother & father).

if we wish to use a form to display a selected descendant & their ascendants (all at the same time & with images) then we require all data to be held in a single tuple(record).
that's a lot of joins, i think that a practical number of generations would be four(1 descendant, 2 parents, 4 grand parents, 8 great grand parents) that's fifteen people.
if we add another generation (16 great great grand parents) that's thirty one people, too many joins & too much to display at one time.
note: any number of generations may be stored in the database.

as indicated by other contributors you should be using a modern database with recursive capabilities, embedded HSQLDB is only ok for learning & useful for uploading demos/examples.

[PurplePix]

Chrisb, I understand what everyone is telling me about doing this all wrong and I should be using a commercial database for Genealogy. Much of my life people have told me that what I am doing is wrong, yet I have been very successful in achieving what is not possible. Your Purple.odb is something I need to spend time with. Unlike the horse pedigree, your solution looks like it will work. Being analytical by nature I have to spend some time to understand how it works. At least in Calc there is enough tools to help you make progress. Base on the other hand throws up cryptic messages, having seen enough of them I am beginning to understand a little of what they mean.

The one thing that everyone overlooks is the user interface, being able to produce the look and actions I want go a long way to satisfaction.

The Lineage sheet is the last sheet I want to add photos to, I know it is possible right now I am looking for that solution and you may have already helped me get there. In the beginning it looks simple, I have data here that I need to display over there, but I am told you can't get there from here.

Us beginners just don't know what questions to ask because we haven't come up with the right question yet. I am mostly in Calc and trying to use the database as a bridge to get to where I want to be.

[Hagar Delest]

Have a look at Gramps. I use on GNU/linux but there is a version for Windows too. Free, open source and quite good.

[PurplePix]

Hagar, I looked at Gramps, and yes it seems to have all kinds of bells and whistles. After spending over an hour trying to enter information, I gave up. For me it was like trying to program a database, and I didn't like the interface so I put it aside.
In the early 70's I took a BASIC programming course, on an Apple 2E. I would go home and convert ASCII to PETSCII to run on my VIC 20 to do my homework. I would then convert it back to ASCII so it would run on the Apple 2E. I did write a few small programs and learn't how to improve the interface for user ease of program use (I know just enough programming to be dangerous). My Family tree is about entering the data, then using the least number of key strokes to extract the data I want. It is not perfect, but then I have watched a $100,000 engine fail because the programmer didn't test for over pressure in the cylinder because of unregulated fuel delivery.

[Hagar Delest]

A family tree is a database!
You are using a spreadsheet to mimic a database. But well, if you prefer to do it that way.

[PurplePix]

Chrisb, I need help to understand your code in Purple.odb
In the Database your last part --- (if i have the table "tPersonPhoto" with the fields "PersonID", "Filepath" & wish to show the picture then:
--repetitive code which adds images to the above) does what I want, except with my data it doesn't give me the expected results.
I have replicated your FTP, tFilter, tPerson and tPersonPhoto following your file structure with my data.
My program logic is to use the FID (FamilyID) as a one to many (parents and children). Each person has a unique ID "PersonID" with children linked as a CHID "Childof" of their parents FamilyID.
I am prepared to go at it from either direction as long as I get the expected results.
I have spent much of the weekend attempting to get the results that work for me. I used your data and put it into a blank Family tree for a better understanding. Because I started at Person 2 and Family number 2 to keep ID numbers the same as row numbers for simplicity, it does have some challenges with others data.
I can only add 3 pictures

[PurplePix]

Continued from above
In your FTP table FID 2&3 and also 13&14 had the same parents for two different families. Running your code (which is brilliant) didn't provide the data (once I wrapped my head around what was happening) I would expect for extracted data. I also couldn't get it to produce the expected data with my information.
Your approach that a child only has one set of biological parents is different than my process of one family has one set of parents and children of that family.
With your code it might seem like a simple process of modifying it to suit (I am still struggling with SQL). The select from and Bracketed code sets up aliasing with a. Each join then does a sub select of row records based on the "CHID" from "FTP". (Now here is the magic) b on b = a. I follow that as b (assign an alias) on b."CHID" (that seems to b to "CHID" and that is made equal to a form with in the opening brackets.
I have attached some screen shots to show what it looks from my side. When I move the backslash star down to include the next row the data goes from a single to multiple rows.

[PurplePix]

Hagar, I would use a database but simple tasks in SQL I find incredibly difficult. I was able to extract the data in a spreadsheet even though some of the formulas were quite complex.
I understand a database offers lots of possibilities, like identifying people in the same geographic area for example. I am having troubles in the small attached database getting the exact data out of a simple table. If there was more documentation - almost a base for Dummies (it should be for newbies) written at a level that a dedicated person could self learn the basics to advance to a more skilled level.
I like most trying to build a database, are challenged with what questions to ask because we don't know enough to ask the right question.
Years ago I saw a new machine manual from the factory with two chapters removed. When I contacted the factory engineer, he said we all know that information. I said you and I know that information, but look at the first time user. You have removed the bridge that allows them to go from no knowledge, through the information to now what is available. That is where I am at with databases, looking for the bridge to get from here to up there were the information is written for advanced users.

[chrisb]

@PurplePix,

Purple.odb shows how we can trace ancestors.
we created a single record which contains the information required to show persons name, position of person in hierarchy & image location.
if you wish to do the same with families then the rules are identical.

i added the table "tFamilyHead":
"FamilyID" PK.
"PersonID", head of family, usually father but could be mother.
"Tag", a string of text which helps identify a family.

i added the table "tFamilyID_PersonID_RoleID":
"FamilyID" PK references "tFamilyHead" ("FamilyID").
"PersonID" ID of family member.
"RoleID" role played by family member (father, mother, daughter, son).

initially i filled these tables directly but later added a form 'fFamily' to simplify the process for you.

the view "vSelectedFamily" created from the query "qSelectedFamily" selects the required data & the query "qTree_Family" produces the final result, i restricted the number of family members to 14.

one of the families i created has 5 members, another has 4 members and all others have 3 members.

i used LibreOffice 7.4.6.2 (x64) on windows 10 19045 & it's been a horrific experience.
there are a huge number of bugs in this version & the apparent lack of supervision at the Document Foundation seems to suggest that the Base module is headed for a lingering death.

Purple2.odb

(42.61 KiB) Downloaded 259 times

[Mountaineer]

...
If there was more documentation - almost a base for Dummies (it should be for newbies) written at a level that a dedicated person could self learn the basics to advance to a more skilled level.
...

You know the guides at the LibreOffice-documetation?
https://documentation.libreoffice.org/e ... mentation/
(I read you use OpenOffice, but concerning Base the main difference I see, is integration of Oracles Report designer and the possibility to use firebird embedded)

The second problem is Documentation on Base being only the first step. Base is no database, but a module to access several databases from text to dbase, HSQLDB up to mySQL/MariaDB or PostgreSQL somewhere on the net. And all this databases have own dialects of SQL (quite restricted for text and dbase). So you need a text on SQL also, best with comments on your database. ( And there actually is/was SQL for Dummies )

[PurplePix]

Chrisb, I saw you posted Purple2.odb, wow what an incredible resource you have provided. I did have a quick look but I was looking at a breakthrough moment with Purple.odb and I needed to remain focused on trying to understand how your code worked - it's brilliant.

Based on what you have posted I would say that my request has been solved. I would like to keep it open so that I could post some insight in how I was able to use the information you provided, so that it might help someone else struggling to make progress on something they are working on.
I kept going back to Purple.odb using backslash star to caption out most of the code so I could work on one line at a time, comparing your results to your table data. I have been busy and spending many hours each day working on my solution most days with little or no progress. I am not there yet but can see and work the logic to get the results I want. I had said earlier we both had a different process, being able to wrap my head around what you were doing and how I could get it to work for me, has and continues to be a struggle, but I am making progress. I can't thank you enough for the effort you put in to help me.

Also thank you for others who made comments, each comment is an opportunity to consider a different point of view.

[chrisb]

@PurplePix,

it's good to hear that things are beginning to fall into place for you.
playing with code & limiting selection is indeed an excellent way to gain understanding.

i made an error in Purple2.odb, the query "qTree_Family" will show zero data if a family has no father.
please replace with:

Code: Select all

--qTree_Family
select * from
	(select "FamilyID2" from "tFilter" where id = 1) z
left join
	(select "FamilyID", "Person" p1, "Role" r1, "FilePath" pic1 from "vSelectedFamily" where pos = 1) a on z."FamilyID2" = a."FamilyID"  --father
left join
	(select "FamilyID", "Person" p2, "Role" r2, "FilePath" pic2 from "vSelectedFamily" where pos = 2) b on z."FamilyID2" = b."FamilyID" --mother
left join
	(select "FamilyID", "Person" p3, "Role" r3, "FilePath" pic3 from "vSelectedFamily" where pos = 3) c on z."FamilyID2" = c."FamilyID" --child
left join
	(select "FamilyID", "Person" p4, "Role" r4, "FilePath" pic4 from "vSelectedFamily" where pos = 4) d on z."FamilyID2" = d."FamilyID" --child
left join
	(select "FamilyID", "Person" p5, "Role" r1, "FilePath" pic5 from "vSelectedFamily" where pos = 5) e on z."FamilyID2" = e."FamilyID" --child
left join
	(select "FamilyID", "Person" p6, "Role" r1, "FilePath" pic6 from "vSelectedFamily" where pos = 6) f on z."FamilyID2" = f."FamilyID" --child
left join
	(select "FamilyID", "Person" p7, "Role" r1, "FilePath" pic7 from "vSelectedFamily" where pos = 7) g on z."FamilyID2" = g."FamilyID" --child
left join
	(select "FamilyID", "Person" p8, "Role" r1, "FilePath" pic8 from "vSelectedFamily" where pos = 8) h on z."FamilyID2" = h."FamilyID" --child
left join
	(select "FamilyID", "Person" p9, "Role" r1, "FilePath" pic9 from "vSelectedFamily" where pos = 9) i on z."FamilyID2" = i."FamilyID" --child
left join
	(select "FamilyID", "Person" p10, "Role" r1, "FilePath" pic10 from "vSelectedFamily" where pos = 10) j on z."FamilyID2" = j."FamilyID" --child
left join
	(select "FamilyID", "Person" p11, "Role" r1, "FilePath" pic11 from "vSelectedFamily" where pos = 11) k on z."FamilyID2" = k."FamilyID" --child
left join
	(select "FamilyID", "Person" p12, "Role" r1, "FilePath" pic12 from "vSelectedFamily" where pos = 12) l on z."FamilyID2" = l."FamilyID" --child
left join
	(select "FamilyID", "Person" p13, "Role" r1, "FilePath" pic13 from "vSelectedFamily" where pos = 13) m on z."FamilyID2" = m."FamilyID" --child
left join
	(select "FamilyID", "Person" p14, "Role" r1, "FilePath" pic14 from "vSelectedFamily" where pos = 14) n on z."FamilyID2" = n."FamilyID" --child

[PurplePix]

Good morning Chrisb, I will make the correction.

[PurplePix]

People like me come to the forum looking for help solve a problem in the absence of good documentation.

In 2016 I began making a Family tree in a calc spreadsheet, my first encounter with the forum I was told I should be using a commercial Database program. My need at that time and even today did not warrant a program that could handle thousands of people entries. I looked at making a small database to meet my needs, but I was not able to successfully make any progress. I continued with the spreadsheet, gathering data that always had the possibility to become a database in the future.

A cousin used a commercial data base, and with software and computer upgrades lost access to all his information. Open Office being open source seemed to offer a great alternative.

While searching the internet, I have found many people looking to make a simple Family Tree in Excel and other program suites. In the early stages my brother was running Excel so I had both an Open Office and Excel versions in both programs. Not having access to newer versions of Excel, I convinced my brother to switch to the Open Office Calc version as new features were added.

I came to the forum looking to add photos to my main Family Display page, I was told then that pictures in spreadsheets were not possible, and again I should be using a Database. With help from Villeroy and Chrisb who made a Database in 2021 based on what I was doing which I found in 2023. I had help along the way with SQL and aliased code for the Display sheet and photos (which I can't find in the forum) so I knew photos in a spread sheet was possible, but lacked the skill to put them on the Lineage page. Chrisb came to the rescue and posted code that even though different from what I needed, showed it was possible to achieve what I was trying to do.

After many hours wrestling with the code, I was able to make it work for me. In working on my Family Tree I see there is a need for something like I have put together. Mine handles 1500 people and 750 Families, it uses simple data entry in two (People and Families) sheets. Many have said Calc is unable to handle complex situation, I disagree. A spreadsheet has no problem with numbers and text, pictures are possible with a small Database. Chrisb has posted a Family Tree Database (Purple2.odb), there is Gramps and my project which started in 2016 has just over 1000 people and 370 Families, 452 Images (stored in a folder on my computer). I am sure I will make improvements over time, but for now I believe it would be good to look through the requests I have put in to understand some of the challenges.

To understand my last request this is part of what my code looks like, the brackets in the comment sections are values not required and I will look at cleaning up the code to see if I can leave them out. The output is all in one line, so I added the Child## to give me a reference what was produced by each line of code. My Lineage sheet goes out five generations, I am only taking the images out three generations to prevent it from becoming cluttered. The Family Display, Who is and Lineage sheets can also be text only if that is what I want.
/*This is where the magic begins */

select *
from
( --target child and parents
select "FamilyID", "P_FathersID", "P_MothersID" , "Childof","PersonPhoto", "Child01" "a"
from "FamilyTreePhotos"
where "FamilyID" = (select "F_FamilyID" from "tbl_FILTER_Lineage" where ID_F = 1)

) a

/* the columns you need are identified in the SELECT statement */
left join /*Family 01 Father FID = 178 P_Father = (503) P_Mother = (504) Childof = 59 PersonPhoto = 178 b = (205) */
(select "FamilyID", "P_FathersID", "P_MothersID" ,"Childof" ,"PersonPhoto" "Photo01Father", "Child02" "b" from "FamilyTreePhotos") b on b."FamilyID" = a."P_FathersID"
left join /*Family 01 Mother FID = 204 P_Father = (584) P_Mother = (582) Childof = 83 PersonPhoto = 204 c = ( ) */
(select "FamilyID" , "P_FathersID", "P_MothersID" ,"Childof","PersonPhoto" "Photo01Mother", "Child03" "c" from "FamilyTreePhotos") c on c."FamilyID" = a."P_MothersID"

/* in the individual SELECT statements, and because of how my table was constructed (FamilyID on the left that included the Father, Mother and Children - used in the Family display sheet for image display) and PersonID, Childof (connection to family) and photo on the right. created a level of complexity for the Lineage sheet project. */

/* Things to remember, alias letters can not be reused in different SELECT statements. Progression is achieved, for example (b ON b."FamilyID" = a."P_FathersID" ) b included everything in the first 'LEFT JOIN'

up to the 'FROM'. Next the 'ON b.' takes one column from the SELECT "FamilyID" and assigns it the value of a."P_FathersID" from the initial SELECT where 'a' was assigned.

Earlier requests
viewtopic.php?t=81349&hilit=PurplePix Identify cell reference with same data
viewtopic.php?t=96928&hilit=PurplePix Connect a Calc cell to an inserted Database field
viewtopic.php?t=100395 extract next record from query to populate spre
viewtopic.php?t=104387&hilit=PurplePix Blank cells in table prevent photo display
This problem for me is solved, that you for everyone that offered suggestions, especially Chrisb for his magic code.

Code: Select all

select *
from
(	--target child and parents
	select "FamilyID", "P_FathersID", "P_MothersID"	, "Childof","PersonPhoto", "Child01" "a"
	from "FamilyTreePhotos"
	where "FamilyID" = (select "F_FamilyID" from "tbl_FILTER_Lineage" where ID_F = 1)

) a			/*Family 01 Father	FID = 70  P_Father = 178  P_Mother = 204  Childof = (27) PersonPhoto = (Needed)  a = (205) */

							/*    FAMILY 1    abc */
left join	/*Family 01 Father	FID = 178  P_Father = (503)  P_Mother = (504)  Childof = 59 PersonPhoto = 178  b = (205) */
	 (select "FamilyID", "P_FathersID", "P_MothersID"	,"Childof" ,"PersonPhoto" "Photo01Father", "Child02" "b" from "FamilyTreePhotos") b on b."FamilyID" = a."P_FathersID" 
left join	/*Family 01 Mother	FID = 204  P_Father = (584)  P_Mother = (582)  Childof = 83 PersonPhoto = 204  c = ( ) */
	 (select  "FamilyID" , "P_FathersID", "P_MothersID"	,"Childof","PersonPhoto" "Photo01Mother", "Child03" "c" from "FamilyTreePhotos") c on c."FamilyID" = a."P_MothersID"

							/*    FAMILY 2    def */
left join	/*Family 02 Family	FID = 59  P_Father = 137  P_Mother = 176  Childof = 0 PersonPhoto = (Needed)  d = (180 ) */
	 (select "FamilyID" ,"P_FathersID","P_MothersID", "Childof","PersonPhoto", "Child04" "d" from "FamilyTreePhotos") d on d."FamilyID" = b."Childof"
left join	/*Family 02 Father	FID = 137  P_Father = (193)  P_Mother = (210)  Childof = 47 PersonPhoto = 137  e = ( ) */
	 (select "FamilyID" ,"P_FathersID" ,"P_MothersID", "Childof" ,"PersonPhoto" "Photo02Father" , "Child05" "e" from "FamilyTreePhotos") e on e."FamilyID" = d."P_FathersID" 
left join	/*Family 02 Mother	FID = 176  P_Father = (490)  P_Mother = (491)  Childof = 130 PersonPhoto = 176  f = ( ) */
	 (select "FamilyID", "P_FathersID", "P_MothersID", "Childof","PersonPhoto" "Photo02Mother",  "Child06" "f" from "FamilyTreePhotos") f on f."FamilyID" = d."P_MothersID"
	
						/*    FAMILY 3    ghi */
left join	/*Family 03 Family	FID = 83  P_Father = 233  P_Mother = 227  Childof = (36) PersonPhoto = (Needed)  g = (180 ) */
	 (select "FamilyID" ,"P_FathersID","P_MothersID", "Childof","PersonPhoto", "Child07" "g" from "FamilyTreePhotos") g on g."FamilyID" = c."Childof"
left join	/*Family 03 Father	FID = 233  P_Father = (672)  P_Mother = (159)  Childof = 92 PersonPhoto = 233  h = ( ) */
	 (select "FamilyID" ,"P_FathersID" ,"P_MothersID", "Childof" ,"PersonPhoto" "Photo03Father" , "Child08" "h" from "FamilyTreePhotos") h on h."FamilyID" = g."P_FathersID" 
left join	/*Family 03 Mother	FID = 277  P_Father = (785)  P_Mother = (786)  Childof = 95 PersonPhoto = 277  i = ( ) */
	 (select "FamilyID", "P_FathersID", "P_MothersID", "Childof","PersonPhoto" "Photo03Mother",  "Child09" "i" from "FamilyTreePhotos") i on i."FamilyID" = g."P_MothersID"

						/*    FAMILY 4    jkl */
left join	/*Family 04 Family	FID = 47  P_Father = 113  P_Mother = 126  Childof = (0) PersonPhoto = (Needed)  j = (133 ) */
	 (select "FamilyID" ,"P_FathersID","P_MothersID", "Childof","PersonPhoto", "Child07" "j" from "FamilyTreePhotos") j on j."FamilyID" = e."Childof"
left join	/*Family 04 Father	FID = 113  P_Father = (314)  P_Mother = (313)  Childof = 25 PersonPhoto = 113  k = ( ) */
	 (select "FamilyID" ,"P_FathersID" ,"P_MothersID", "Childof" ,"PersonPhoto" "Photo04Father" , "Child08" "k" from "FamilyTreePhotos") k on k."FamilyID" = j."P_FathersID" 
left join	/*Family 04 Mother	FID = 126  P_Father = (352)  P_Mother = (351)  Childof = 138 PersonPhoto = 126  l = ( ) */
	 (select "FamilyID", "P_FathersID", "P_MothersID", "Childof","PersonPhoto" "Photo04Mother",  "Child09" "l" from "FamilyTreePhotos") l on l."FamilyID" = j."P_MothersID"

						/*    FAMILY 5    mno */
left join	/*Family 05 Family	FID = 130  P_Father = 362  P_Mother = 359  Childof = (47) PersonPhoto = (130)  m = ( ) */
	 (select "FamilyID" ,"P_FathersID","P_MothersID", "Childof","PersonPhoto", "Child10" "m" from "FamilyTreePhotos") m on m."FamilyID" = f."Childof"
left join	/*Family 05 Father	FID = 362  P_Father = (982)  P_Mother = (983)  Childof = 129 PersonPhoto = 362  n = ( ) */
	 (select "FamilyID" ,"P_FathersID" ,"P_MothersID", "Childof" ,"PersonPhoto" "Photo05Father" , "Child11" "n" from "FamilyTreePhotos") n on n."FamilyID" = m."P_FathersID" 
left join	/*Family 05 Mother	FID = 369  P_Father = (976)  P_Mother = (977)  Childof = 128 PersonPhoto = 126  o = ( ) */
	 (select "FamilyID", "P_FathersID", "P_MothersID", "Childof","PersonPhoto" "Photo05Mother",  "Child12" "o" from "FamilyTreePhotos") o on o."FamilyID" = m."P_MothersID"

						/*    FAMILY 6    pqr */
left join	/*Family 06 Family	FID = 92  P_Father = 655  P_Mother = 231  Childof = (0) PersonPhoto = (Needed)  p = ( ) */
	 (select "FamilyID" ,"P_FathersID","P_MothersID", "Childof","PersonPhoto", "Child13" "p" from "FamilyTreePhotos") p on p."FamilyID" = h."Childof"
left join	/*Family 06 Father	FID = 655  P_Father = ( )  P_Mother = ( )  Childof = 90 PersonPhoto = 655  q = ( ) */
	 (select "FamilyID" ,"P_FathersID" ,"P_MothersID", "Childof" ,"PersonPhoto" "Photo06Father" , "Child14" "q" from "FamilyTreePhotos") q on q."FamilyID" = p."P_FathersID" 
left join	/*Family 06 Mother	FID = 231  P_Father = (663)  P_Mother = (664)  Childof = 91 PersonPhoto = 231  r = ( ) */
	 (select "FamilyID", "P_FathersID", "P_MothersID", "Childof","PersonPhoto" "Photo06Mother",  "Child15" "r" from "FamilyTreePhotos") r on r."FamilyID" = p."P_MothersID"

						/*    FAMILY 7    stu */
left join	/*Family 07 Family	FID = 95  P_Father = 267  P_Mother = 268  Childof = (36) PersonPhoto = (Needed)  s = ( ) */
	 (select "FamilyID" ,"P_FathersID","P_MothersID", "Childof","PersonPhoto", "Child13" "s" from "FamilyTreePhotos") s on s."FamilyID" = i."Childof"
left join	/*Family 07 Father	FID = 267  P_Father = (764)  P_Mother = (765)  Childof = 93 PersonPhoto = 267  t = ( ) */
	 (select "FamilyID" ,"P_FathersID" ,"P_MothersID", "Childof" ,"PersonPhoto" "Photo07Father" , "Child14" "t" from "FamilyTreePhotos") t on t."FamilyID" = s."P_FathersID" 
left join	/*Family 07 Mother	FID = 268  P_Father = (160)  P_Mother = (766)  Childof = 94 PersonPhoto = 268  u = ( ) */
	 (select "FamilyID", "P_FathersID", "P_MothersID", "Childof","PersonPhoto" "Photo07Mother",  "Child15" "u" from "FamilyTreePhotos") u on u."FamilyID" = s."P_MothersID"