What about

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 d.DEPARTURE_DATE is null

HTH
Juan Jose

--- In firebird-support@yahoogroups.com, "Clay Shannon"
<cshannon@d...> wrote:

> 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]