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)