You need a correlated subquery (the main reason I am using Firebird)...

Try this:

select p.firstname, p.lastname, a.admit_date, a.ADMISSION_ID,
a.ADMIT_ORDINAL
from patient_admissions a
join patients p on p.PATIENT_ID = a.admission_id
group by p.firstname, p.lastname, a.admit_date, a.ADMISSION_ID,
a.ADMIT_ORDINAL
having max(a.admit_date) > (select max(d.DEPARTURE_DATE) from departures d
where d.admission_id = a.admission_id)

Since I do not have your schema I could not quickly replicate. If you get
errors post and I will try to guide you.

Aaron


-----Original Message-----
From: Clay Shannon [mailto:cshannon@...]
Sent: Monday, March 28, 2005 2:33 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Calling all SQL gurus


I want to retrieve a list of patients currently in the hospital. I can do
this (I think) by comparing admit dates with departure dates-every patient
who has an admit_date that is later than their most recent departure date is
here. However, the SQL I've cobbled together is bad:



I've tried all the derivations of the following I can think of, and nothing
works:



select p.firstname, p.lastname, a.admit_date, a.ADMISSION_ID,
a.ADMIT_ORDINAL, d.departure_date

from patient_admissions a

join departures d on d.admission_id = a.ADMISSION_ID

join patients p on p.PATIENT_ID = a.admission_id

where max(a.admit_date) > max(d.DEPARTURE_DATE)



--if I replace "where" with "having," that also fails.



Clay Shannon,

Dimension 4 Software





[Non-text portions of this message have been removed]




Yahoo! Groups Links