Left join oldest (min) record

[PRMiller2]

My SQL is not where it should be -- I've done a search and can't seem to get the syntax of this quite right.

I am selecting all Claims from my ClaimDetail table, and related Contacts in the ContactHistory table, but only the most recent one. In a nutshell:

1. If a claim has not had any contacts, I want to show it
2. If it has had contacts, I only want to show the most recent one

Here's what I've got so far:

SELECT a.ClaimDetailID, b.ContactStart, a.PrescriberID
FROM ClaimDetail AS a 
LEFT JOIN ContactHistory AS b ON a.ClaimDetailID = b.ClaimDetailID_fk
AND b.ContactStart = (SELECT MAX(b1.ContactStart) AS NewestContact
FROM ContactHistory b1 WHERE b.ClaimDetailID_fk = b1.ClaimDetailID_fk);

I'm receiving this error: Syntax error in query expression "

Where am I going wrong?

Thanks,
Paul

 

[PHV]

What about simply this ?

SELECT a.ClaimDetailID, Max(b.ContactStart) AS NewestContact, a.PrescriberID
FROM ClaimDetail AS a 
LEFT JOIN ContactHistory AS b ON a.ClaimDetailID = b.ClaimDetailID_fk
GROUP BY a.ClaimDetailID, a.PrescriberID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PRMiller2]

That did it, thanks!