How do you query this complicated thing?

sprion · January 5th, 2005, 09:08 PM

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

jemacc · January 6th, 2005, 12:36 AM

can you post your full table structure, it appears you have additional columns missing. I see your are using now() instead of getdate() are you using access,SQL or any other database?

Jaime E. Maccou

sprion · January 6th, 2005, 01:01 AM

Hey thanks for replying.. I had used the "Ranking Table" so that the personnel queried from the MTL records can be arranged in order.

I wanted personnel of higher ranking to be at the top when getting results.

Rank Class Rank Name
1 DXO10S
2 DXO9S
3 DXO6B
4 2LT
5 2WO
6 S/MSG
7 MSG
8 S/SSG
9 SSG
10 1SG
11 S/2SG
12 2SG
13 3SG
14 CPL
15 LCP
16 PTE
17 REC

thanks for your time :)

sprion · January 6th, 2005, 01:02 AM

Hi, yeah I'm using ACCESS, thanks again.

jemacc · January 6th, 2005, 02:04 AM

Try your query based on these examples. You should have primary keys on the tables when using these methods

First create the view a or query

Code:

create view qryproductcount
as
SELECT Count(Products.ProductID) AS CountOfProductID, Products.CategoryID
FROM Products
GROUP BY Products.CategoryID;

then create the select statements

Code:

SELECT Prod1.CategoryID, Prod1.CountOfProductID, (Select Count(*) from qryProductCount Where [CountofProductID] >[Prod1].[CountofProductID])+1 AS Ranking
FROM qryProductCount AS Prod1
ORDER BY Prod1.CountOfProductID DESC;

you can find examples just like this here:
http://www.microsoft.com/downloads/d...displaylang=en

Jaime E. Maccou

jemacc · January 6th, 2005, 02:06 AM

I forgot to add; try posting your questions in the access forum, you may get better results and or answers

Jaime E. Maccou