Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    2

    Question Unanswered: query help for newbie


    I have a genealogy table that is as compact as I can get it.
    Structure is as follows:
    ID# (primary key, autonumber)
    last name
    first name
    middle name
    DOB
    DOD
    DOM (date of marriage)
    SPOUSE ID# (CORRESPONDS TO ANOTHER ID#) FATHER ID# (CORRESPONDS TO ANOTHER ID#)
    MOTHER ID# (CORRESPONDS TO ANOTHER ID#)

    My problem:
    How do I link these through a query?
    Pardon my newbieness.
    thanks for help.
    Stevesgmarty@mindspring.com

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    steve, you'll need a left outer join from the table to itself, one left outer join per relationship that you want to show

    select p.firstname||' '||p.lastname as person
    , s.firstname||' '||s.lastname as spouse
    , f.firstname||' '||f.lastname as father
    , m.firstname||' '||m.lastname as mother
    from genealogy p
    left outer join genealogy s
    on p.spouseid = s.id
    left outer join genealogy f
    where p.fatherid = f.id
    left outer join genealogy m
    where p.motherid = m.id

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    2

    Smile

    THANKS RUDY!!!!!!!
    I solved it!
    Access XP can be a mindbender!?!?

    FATHER
    SELECT p.lname & ' ' & p.fname & ' ' & p.mname AS person, f.lname & ' ' & f.fname & ' ' & f.mname AS father
    FROM genealogy AS p LEFT JOIN genealogy AS f ON p.fid = f.id;

    MOTHER
    SELECT p.lname & ' ' & p.fname&' '&p.mname AS person, m.lname & ' ' & m.fname&' '&m.mname AS mother
    FROM genealogy AS p LEFT JOIN genealogy AS m ON p.mid=m.id;

    SPOUSE
    SELECT p.lname & ' ' & p.fname & ' ' & p.mname AS person, s.lname & ' ' & s.fname & ' ' & s.mname AS spouse, s.dom AS dom
    FROM genealogy AS p LEFT JOIN genealogy AS s ON p.sid = s.id;

    COMBINED RELATIONSHIPS
    SELECT p.lname & ' ' & p.fname & ' ' & p.mname AS person, p.DOB AS DOB, p.DOD AS DOD, f.lname & ' ' & f.fname & ' ' & f.mname AS father, m.lname & ' ' & m.fname & ' ' & m.mname AS mother, s.lname & ' ' & s.fname & ' ' & s.mname AS spouse, s.DOM AS dom
    FROM ((genealogy AS p LEFT JOIN GENEALOGY AS f ON p.FID = f.ID) LEFT JOIN GENEALOGY AS m ON p.MID = m.ID) LEFT JOIN GENEALOGY AS s ON p.SID = s.ID;


    COOL, HUH???
    STEVE:

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •