[Solved] Help with SQL query

[PurplePix]

I have made a Family tree spreadsheet with two data collection sheets, Person & Families.There are a number of display sheets that use complex dynamic formulas to extract data from the collection sheets and populate the display sheet being used. This only requires a Family number or Person number depending on the sheet. For example the Display sheet for a Family shows the parents, their children and spouses and other data form the people and Families sheets.

I added the ability to also include photos, again with a single entry and a page refresh. This required a small data base to allow this to happen and much trial and error.

A few years ago I added a Lineage sheet to show five generations on both sides of the family, again with a single entry. I would like to add pictures to this sheet also.

I struggle with SQL queries and have not found any good source of documentation to help me get to a solution.

The Database tables are "FamilyTreePhotos" (FamilyID -Key & 1 to Many, P_FathersID, P_MothersID), "FamilyTreePersonPhotos" (PersonID - Key, Childof - Many to one, PersonPhoto).

The screen shot shows at the upper right "FathersParents" and "MothersParents" which are correct for the "FamilyID" in the upper left. With the "FathersParents" and "MothersParents" ID's I want to get the Father and Mother ID's like for Family 70 that came from a Filter table. I don't have any idea how to get to this next step, it has taken me days and weeks to get to the current query through much trial and error and cryptic error messages.

Achieving the next step should allow me to work forward from there.

Code: Select all

SELECT
	p."FamilyID"
-- Parents
	, p."P_FathersID"
	, p."P_MothersID"
--Parents
	, p1."PersonPhoto" "Photo01Father"
	, p2."PersonPhoto" "Photo02Mother"
	, p3."Childof" "FathersParents"
	, p4."Childof" "MothersParents"
	, p5."Childof" "FathersParents" 
FROM
	"FamilyTreePhotos" p
	left outer join (select "FamilyID", "PersonPhoto" from "FamilyTreePhotos") p1 on p."P_FathersID" = p1."FamilyID"
	left outer join (select "FamilyID", "PersonPhoto" from "FamilyTreePhotos") p2 on p."P_MothersID" = p2."FamilyID"
	left outer join (select "FamilyID", "Childof" from "FamilyTreePhotos") p3 on p."P_FathersID" = p3."FamilyID"
	left outer join (select "FamilyID", "Childof" from "FamilyTreePhotos") p4 on p."P_MothersID" = p4."FamilyID"
	left outer join (select "FamilyID", "Childof" from "FamilyTreePhotos") p5 on p."P_FathersID" = p5."FamilyID"
WHERE
	 "FamilyID" = (SELECT "F_FamilyID" FROM "tbl_FILTER_Lineage" where ID_F = 1)

Lineage Query-r.jpg (78.36 KiB) Viewed 3552 times

Lineage sheet-mu-r.jpg (69.09 KiB) Viewed 3552 times

[Villeroy]

http://forum.openoffice.org/en/forum/vi ... 928#p78928 (horses)

[PurplePix]

Sorry for the slow reply, I had a short notice out of town trip and no internet.
In my request I asked for help, and maybe I should have been more clear because I struggle to understand the basic principals of SQL and the query.
In your example which seems very different. You have the ID/FamilyID, Name/not used, DamID/MothersID and SireID/FathersID. An important part of my need is to from the Family number entered in the Spreadsheet, which is picked up by the Lineage filter and used in the SQL query using the FamilyID number, extracts the P_FathersID, P_MothersID, the Fathers and Mothers photos and the ID number of both their parents families. It is these two ID numbers that I need to use to extract information about their families, the same as already done as shown.

My thought would to again use p. "FamilyID", add additional p5 & p6 which doesn't give me the expected result.

The extracted "FathersParents" 59 (, p5."Childof" "FathersParents") & (left outer join (select "FamilyID", "FamilyID" from "FamilyTreePhotos") p6 on p."P_FathersID" = p6."FamilyID") gives an error : Data content could not be loaded. Column already exists: FamilyID in statement [SELECT p.FamilyID ..........

What am I doing wrong? (left outer join (select "FamilyID", "**********" from "FamilyTreePhotos") p6 on p."P_FathersID" = p6."FamilyID"). "**********" using "FamilyID" gives an error, P_FathersID gives 59 with 49 is expected, "Childof" gives the same result.

I meed a dumbed down replay of which columns are used in the query and the reason they are used. Looking at (left outer join (select "FamilyID", "Childof" from "FamilyTreePhotos") p4 on p."P_MothersID" = p4."FamilyID") FamilyID because it is the family we are dealing with, Child of makes no real sense other than it might lead us to her family, FROM "FamilyTreePhotos" - thats the table we are using, "MothersID" because we are trying to find her family = FamilyID and 83 is the expected number.
How do I use the Mother & Fathers Family ID to extract their parents etc. while data exists or up to 5 generations?

[PurplePix]

After sleeping on the problem, and not being able to adjust the SQL, I copied and pasted my database table and edited the names to match the name of the table I was replacing and ran the query in SQL view.
Some of the extracted columns were correct but most were wrong, so it is back to getting the right code or an understanding of how to produce the correct code.

Query1-r.jpg (100.43 KiB) Viewed 3304 times

[PurplePix]

Using the earlier code that produces a family number for the family entered in the spreadsheet and taken from the Filter table p3 in the SELECT and FROM produces a new number that I would like to replicate the result for the next family. Any help or suggestions what I should put would be appreciated.
SELECT
, p3."Childof" "FathersParents"
,p6. "***************" "***************" HOW DO I USE FATHERSPARENTS from p3
FROM
"FamilyTreePhotos" p
left outer join (select "FamilyID", "Childof" from "FamilyTreePhotos") p3 on

p."P_FathersID" = p3."FamilyID" -- This gives the FathersParents FamilyID
HOW DO I USE FATHERSPARENTS from p3 to get the fathers Parents
left outer join (select "*********", "*******" from "FamilyTreePhotos") p3 on p."***********"

= p3."***********"

[PurplePix]

The solution can be found in
viewtopic.php?p=536911#p536911