Syntax for Double Join

  • Thread starter Thread starter Brad Wood
  • Start date Start date
B

Brad Wood

I have two tables: Kid and Parent

The Kid table has an optional foreign key into the parent
table for FatherID and another optional foreign key for
MotherID (a child may only have one parent).

I would like to write a query that would return:
Kid.Name | Parent.Name | Parent.Name
kiddy daddy mommy

So far I am only able to write one that returns:
Kid.Name | Parent.Name
kiddy daddy
kiddy mommy

The one for the above result set is:
SELECT Kid.Name, Parent.Name
FROM Parent INNER JOIN Kid ON
(Parent.ParentID=Kid.FatherID) OR
(Parent.ParentID=Kid.MotherID);

Can anyone show me a query that would give both parents in
one record?
 
Not sure as your Table is slightly unnormalised which I don't use too often.
However, try the SQL:

****
SELECT K.[Name], D.[Name], M.[Name]
FROM
( tblKid AS K LEFT JOIN tblParent AS D
ON K.frg_FatherID = D.ParentID )
LEFT JOIN tblParent AS M
ON K.frg_MotherID = M.ParentID
****

BTW, "Name" is a bad choice for a Field names as almost every object in
Access has the Property "Name" and it will get very confusing when you do
coding.
 
Can you tell me how I should have structured such a
relationship to be normalized?

BTW, these aren't the actual names in my tables; just
trying to simplify for post.
 
1. Create a Table tlkpRelation with Fields & values like:

RelID RelDesc
1 Father
2 Mother
3 Step Father
4 Step Mother
5 Surrogate Father ???
6 Surrogate Mother
.....

(you never know ... )

2. Create a Table tblKidParent with Fields:

frg_KidID FK from tblKid
frg_ParentID FK from tblParent
frg_RelID FK from tlkpRelation

This is used to represent the Many-to-Many relationship
between Kids and Parents. For each Kid, you can create a
number of Records in this Table, each link the Kid to
a "Parent" and the relation between the Kid and
the "Parent". With recombinant families nowadays, a kid
can have more than 2 parents!

3. The FatherID and MotherID in tblKid are no longer
required.

However, it is sometimes covenient to leave the Table
slightly denormalised (like your Table). Provided that
you are aware of the consequence (e.g. someone may insist
that the Database must show all 4 Parents - 2 natural & 2
step - for a particular Kid) and work-arounds, then there
is no problem leaving the Table slightly denormalised.

Perhaps, it will be beneficial for you to do some research
into Relational Database Design Theory at this stage and
find out for yourself when to do what w.r.t. Table
Structure.

Regarding "Name": that's the way to go.

HTH
Van T. Dinh
MVP (Access)
 
Back
Top