View Single Post
 
Old February 2nd, 2005, 07:52 PM
pkipe pkipe is offline
Authorized User
 
Join Date: Jun 2003
Location: Califon, NJ, USA.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I just noticed this unanswered post and hope it's not too late to be of help...

First, the MTL table is fine, but the NextOfKin table needs a primary key that is unique. Because there are two records having the same value for MTL_NRIC, that field won't work for the table's primary key. So I inserted as the first field a new AutoNumber field called pk_MTL_NRIC, and set it to the primary key of the table.

With that change, the following code returns the result set you're looking for:

SELECT MTL.*,
       NextOfKin.*
FROM MTL
LEFT JOIN NextOfKin
  ON MTL.NRIC = NextOfKin.MTL_NRIC;

Here's the result set:

NRIC Rank MTL.Name pk_NextOfKin MTL_NRIC NextOfKin.Name Relationship
3213121Z BG Dick
8321321B SGT Harry 3 8321321B Elaine Spouse
8401234A PTE John 1 8401234A Rose Mother
8401234A PTE John 2 8401234A Jack Father

Hope this helps.

Pete