Join Question

[MarkB2]

I have a patient table, a provider table and a return to work (RTW) table. Both the provider and RTW tables may have many rows for each patient, or none. I am trying to put together a select statement which will return the patient information and the most recent provider, based on begdate, and the most recent RTW episode based on lastdayworked. Following is what I would do if it were a one to one relationship. I am stumped when there are multiple children rows.

SELECT
patient.patid, patient.name,
provider.name, provider.begdate,
rtw.lastdayworked, rtw.returndate
FROM patient
LEFT JOIN provider ON provider.patid=patient.patid
LEFT JOIN rtw ON rtw.patid=patient.patid
WHERE patient.patid='123456'

TIA
Mark

 

[gradley]

You need to match the record with the max date condition.

See if this code works for you:

SELECT 
  patient.patid, patient.name, 
  p.name, p.begdate, 
  rtw.lastdayworked, rtw.returndate
FROM patient
LEFT JOIN provider p ON p.patid=patient.patid
LEFT JOIN rtw ON rtw.patid=patient.patid
WHERE patient.patid='123456'
and p.begdate = (Select Max(p2.begdate) from Provider p2 where p.patid = p2.patid)
and rtw.lastdayworked = (Select Max(r2.lastdayworked) from rtw r2 where rtw.patid = r2.patid)

 

[MarkB2]

Thanx gradley, I came up with basically the same thing as follows:

SELECT 
  patient.patid, patient.name, 
  provider.name, provider.begdate, 
  rtw.lastdayworked, rtw.returndate
FROM patient
LEFT JOIN provider ON provider.patid=patient.patid 
  AND
  provider.begdate=(SELECT MAX(provider.begdate) FROM
  provider WHERE provider.patid = patient.patid)
LEFT JOIN rtw ON rtw.patid=patient.patid 
  AND
  rtw.lastdayworked=(SELECT MAX(rtw.lastdayworked) FROM
  rtw WHERE rtw.patid = patient.patid)
WHERE patient.patid='123456'

The above seems to be working although it has a few quirks. If I have two rtw records with the same lastdayworked, I get two rows returned. Also if I have multiple records and they have NULL lastdayworked, I only get one and I do not know which one will be returned consistantly.

Again Thanx
Mark

 

[gradley]

No problem! Glad you came up with a resolution that works.