How TO: Get one record from a joined table

[mlee1steph]

Hi Everyone,
I have a query that needs to pull data out of 3 tables. Nothing to hard so far. Here the query:
SELECT Patients.PatientId, TERMINATION.DOA, TERMINATION.DOT, PATIENTS.PatientID, PATIENTS.LastName, PATIENTS.FirstName, PATIENTS.COUNSELOR, PATIENTS.MEDICAID_ID, PATIENTS.DOB, COUNSELORS.C_LastName + ', ' + COUNSELORS.C_FirstName as CName
FROM (PATIENTS LEFT JOIN TERMINATION ON PATIENTS.PatientID = TERMINATION.PatientID)
LEFT JOIN COUNSELORS ON PATIENTS.COUNSELOR = COUNSELORS.C_ID
WHERE ActiveClient = 1
order by NAME
Ok, The problem with this query is that the Termination table can contains more that one record for each PatientId, so the result of this query is that if there are 2 records in the Termination table I get two records in the result. I only want ONE record for each Patient. More specifically I will need to look at the TERMINATION.DOA field and get the newest date (this field is a datetime field). Does anyone have any suggestions. Thanks for any help.
Michael

 

[SkipVought]

Hi,

Try MAX on the date

SELECT 
  Patients.PatientId
, Max(TERMINATION.DOA)
, TERMINATION.DOT
, PATIENTS.PatientID
, PATIENTS.LastName
, PATIENTS.FirstName
, PATIENTS.COUNSELOR
, PATIENTS.MEDICAID_ID
, PATIENTS.DOB
, COUNSELORS.C_LastName + ', ' + COUNSELORS.C_FirstName as CName

FROM (    PATIENTS 
LEFT JOIN TERMINATION 
  ON PATIENTS.PatientID = TERMINATION.PatientID) 
LEFT JOIN COUNSELORS 
  ON PATIENTS.COUNSELOR = COUNSELORS.C_ID

WHERE ActiveClient = 1

Group By
  Patients.PatientId
, TERMINATION.DOT
, PATIENTS.PatientID
, PATIENTS.LastName
, PATIENTS.FirstName
, PATIENTS.COUNSELOR
, PATIENTS.MEDICAID_ID
, PATIENTS.DOB
, COUNSELORS.C_LastName + ', ' + COUNSELORS.C_FirstName as CName

[tt][highlight blue][white]
^{~ ~}[/white][/highlight][highlight red][white] To be ^~[/white][/highlight][highlight blue][white]
^~ BE ^~[/white][/highlight][highlight white][blue] safe on the 4^{th ~}[/blue][/highlight][highlight blue][white]
^~ ADVISED ^~[/white][/highlight][highlight red][white] Don't ^~[/white][/highlight][highlight blue][white]
^{~ ~}[/white][/highlight][highlight white][blue] get a ^~[/blue][/highlight]
[highlight red][white] 5^th on the 3^{rd ~}[/white][/highlight]
[highlight white][blue] Or you might not ^~[/blue][/highlight]
[highlight red][white] Come 4^th on the 5^th 4^thwith ^~[/white][/highlight]
[/tt]


Skip,

_[red][/red]

 

[mlee1steph]

Hi Skip,
Thanks for the reply. The Max is a problem because it is a aggregate function. And I can't add all the field as part of the aggregate. Maybe I can create some sort of subquery to get only the Newest date.
Michael

 

[mlee1steph]

I almost forgot something. There can be cases where the there may not be any record in the TERMINATION for the Patientid, I still need these Patients also.
Any ideas? Thanks
Michael