View Single Post
 
Old January 5th, 2005, 06:38 PM
sprion sprion is offline
Registered User
 
Join Date: Jan 2005
Location: Singapore, , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Just how do you do it? Complicated!

The Tables

MTL Table

--------------------------------------------------------------------------------

NRIC RANK NAME
8401234A PTE John
8321321B SGT Harry
3213121Z BG Dick

--------------------------------------------------------------------------------

Next of Kins Table

--------------------------------------------------------------------------------

MTL_NRIC Name Relationship
8401234A ROse Mother
8401234A Jack Father
8321321B Elaine Spouse

--------------------------------------------------------------------------------

I'm trying to return ALL data from each personnel in MTL, whether they have a Next of Kin or not, and if they do not have a Next of Kin, just return a blank field for that column.

In case they have 2 Next of Kins, just return 1 row for that personnel with any 1 Next of Kin.


Below is the SQL code I tried really hard to finally come up with ..

--------------------------------------------------------------------------------

SELECT MTL.NRIC, MTL.Rank, MTL.Name
FROM MTL, (SELECT DISTINCT m.NRIC FROM MTL as m LEFT OUTER JOIN [Next of Kins] ON (m.NRIC = [Next of Kins].MTL_NRIC)) AS o, Ranking
WHERE (MTL.NRIC = o.m.NRIC) AND (Ranking.[Rank Name] Like MTL.[Rank]) AND (MTL.ORD > Now()) AND (MTL.PostedOut <> TRUE)
ORDER BY Ranking.[Rank Class], MTL.Name;

--------------------------------------------------------------------------------

It works but .. does not have any Next of Kin info..

--------------------------------------------------------------------------------

SELECT MTL.NRIC, MTL.Rank, MTL.Name, o.[Next of Kins].Name, o.[Next of Kins].Relationship
FROM MTL, (SELECT DISTINCT m.NRIC FROM MTL as m LEFT OUTER JOIN [Next of Kins] ON (m.NRIC = [Next of Kins].MTL_NRIC)) AS o, Ranking
WHERE (MTL.NRIC = o.m.NRIC) AND (Ranking.[Rank Name] Like MTL.[Rank]) AND (MTL.ORD > Now()) AND (MTL.PostedOut <> TRUE)
ORDER BY Ranking.[Rank Class], MTL.Name;

--------------------------------------------------------------------------------

This has an error as o.[Next of Kins].???? does not return any data as the table 'o' does not SELECT it.
But if I do SELECT them (NOK.Name, NOK.Relationship), it would also include duplicate NRIC in the results, as PTE John has 2 Next of Kins..


Please help.

Yours,
Going Grazy