Genealogy Query

Thought I had this worked out, but turns out it doesn’t :frowning:
I have TWO tables


    This table could have 1 or 2 records (Just Mother, Just Father, or Mother and Father)
    ID_PERSON is the “child”
    ID_PARENT is the Parent (mother or father)


    This table indicates a relationship of some type, where each ID relates to one of those people

For a given Focus_PERSON, they could

  • have one or more partners and have NO CHILDREN with a given partner (ie there would be no entery where FOCUS_PERSON is the ID_PARENT in PARENTS table, but is either ID_PERSON or ID_PARTNER in the PARTNERS table
  • have one or more partners and have one or more children with a given partner (there would be matching records in both tables)
  • have a child where the focus is recorded as a parent, but there is no partner recorded

The problem is that a person could have had multiple partners and had one or more children with each one

What I am trying to accomplish is a single SQL Query that will return PARTNER and CHILD given a single FOCUS_ID

It should return records like this

Partner001 : Child001 : FocusID was a partner with Partner001 and had Child001
Partner002 : * : FocusID was a partner with Partner002 and had no children

Any entries that are the result of Partner001 or Partner002 being involved with any other person should be ignored

The problem is PARENTS… since each PARENT is a single record (or and to make it worse, there could be more than one Mother and/or Father [biological, adopted, step, foster etc)

hope I explained that.

So is the ID for the Focus person always a ID_PERSON in either table? If not, I don’t follow how these two tables are related.

yes. ID_PERSON relates to the FOCUS_ID (ie. the person you need the information about)

I’m trying to create a view with some LEFT JOINS to normalize the PARENT file to be more like the PARTNER file

After re-reading everything again, this doesn’t make sense. You state that PARENTS.PERSON_ID is the child, yet your list of possibilities for a FOCUS person is all about that person’s partners and children. It sounds like the FOCUS person can match to either ID in both tables. If this is not the case, then it would help if you broke this down by each table separately first so i (we?) can get a better understanding.

Sorry… you are corrent

ID=DAVE Parent=Dad
ID=DAVE Parent=Mom
ID=SUZY Parent=Dad
ID=SUZY Parent=StepMom
ID=Fred Parent=Dad

ID=DAD Partner=MOM
ID=DAD Partner=StepMom
ID=DAD Partner=someone

For a given query, the FOCUS_ID= DAD

Partner = Mom Child = Dave
Partner = StepMom Child =Suzy
Partner = * Child = Fred
Partner = Someone Child = *

Ok. In this scenario, what if the record in Parents of “ID=SUZY Parent=Dad” was not there? Would your second result line still be included? In others words, do both parents have to be in the Parents table for a child to be listed as “their” child?

Seems to me you need two tables - person & relationship. The person table holds all the details of the person (obviously) while the relationship table links 2 persons together and has a relationship type as a minimum - it should also include a beginning & termination date for partnerships. Your relationship types are bidirectional e.g. Parent/Child, Husband/Wife, Partner/Partner.

With this you can now query the relationship table for the focus person on either person1 or person2 and get all relationships.

No… since it would be looking for the progeny of “DAD”, Suzy would not appear since “DAD” is not one of her parents

Wayne… there is a PERSON table as you describe, but that is not relavant to this query… once I had the associated ID’s getting the demographics is a snap
And the PARTNERS table contains all the “known” relationships
but just because two people are partners doesn’t mean they had children
and it is possible that only one parent is known

and unlike other Genealogy programs… this one supports the concept of PARTNERS… not “Spouse”, that is just a type of partner

Solved it…
Had to create a VIEW that normailized the PARENTS data to be CHILD, MOTHER, FATHER
then a long query that joined that table with an RELATIONSHIP table to find partners with no children


Like the concept. A partner can be a one night stand?

I ask this question because my father bonked a girl who became pregnant then married her younger sister (true story). How do you manage the relationship with my half-brother/step cousin?

I also wonder how your app can cope with the relationship of a rape victim and the perpetrator when there is a child?

Sorry about the downer, but it does happen and in some states it appears the perpetrator can block the termination of the pregnancy.

Yes it does happen… that is why I am trying to support non-biological (and even same sex marriages)… So far to my knowledge that has not happened on my family tree… and while I am not writing this to be a commerical application, no harm in having a flexible design :slight_smile:

Here is a thought. Starting from your ID_PERSON, I would suggest a RELATIONSHIP table. The key to this new table would be ID_PERSON, partner type, sequential number (in case for example there are more than one “spouse” type relationships). ID_PERSON2 would be the characteristic (there may be others). You can handle pretty much any kind of relationship, up or down in the tree. You can search by partner of by partner2.

In this, Say Arthur is Bob’s father. The table would look something like this:
Arthur, Son, 0, Bob. Bob is Arthur’s first son. Now, you will need to establish some rules to avoid duplicating information. You would not need another entry with Bob, Father, 0, Arthur. Or perhaps it does not matter. You know your goals best.

Partner types would be something like spouse, kin (brother or sister), parent (or any type of more explicit parent: mother, father, step-whatever). You can be as detailed or as generic as you like.

edit: corrected an error in the relationship key.

Tracking 1st born is not a good idea, as future information could contradict that (remember research could go back hundreds of years)

Partners are that a partner, Brother and Sister are siblings not partners. A spouse is a Partner, but a Partner is not always a spouse.

The above problem has been solved by adding a custom view and a specialized query…

For the record each person has 3 types of “relationships” for a genealogical point of view

  • they have parents (biological and/or adopted/step/foster)
  • they have partners (including same-sex, and as Wayne put it, “one night stand”, as it could have produced a child)
  • they have progeny (children that were born of a relationship)

Siblings are someone who shares one or more parents.

I agree with most of what you said. Tracking the first born is an “accidental” result. Let’s say it is simply the first of similar relationships that was recorded. This is more accurate.

I wish my father was still alive, he was an internationally known expert on a few families including of course our own. He used to spend hours on the phone with people from all over the world seeking genealogical information that was sometimes hard to find. I have a copy of the wedding certificate of one of my ancestors signed as witness by Jean Talon, then Intendant of Nouvelle France. (actually the colony was small and he attended all weddings…) My father would would have loved to share ideas with you. He was using a system similar to what I described, all on paper because computers did’nt exist yet. His numbering system allowed him to describe within a few minutes the level at which 2 persons were related. His system was patriarcal, with spouse and children recorded under the patriarcal key.

But as I said, you know your goals best. I was just sharing a different approach for your consideration. Cheers.