At 01:33 PM 28/03/2005 -0600, you 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.

HAVING is a filter on the output set of a grouped query, which this isn't.

The logic problem with your query is that your where clause is looking at
whole table MAXes. You are always going to get all patients if the highest
a.admit_date is higher than the highest d.departure_date, or no patients if
that is not the case.

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) >
(select max(d1.DEPARTURE_DATE from departures d1
where d1.admission_id = a.admission_id)

However in a later message you say:

"there's no record at all in the Departures table until they depart"

So, you're not actually interested in the departure date at all, are you?
Isn't what you are *really* looking for the admissions records that have no
corresponding departures records?

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
where not exists (
select 1 from departures d
where d.admission_id = a.admission_id)

Alternatively (if you like):

select
p.firstname,
p.lastname,
a.admit_date,
a.ADMISSION_ID,
a.ADMIT_ORDINAL,
d.departure_date
from patient_admissions a
join patients p
on p.PATIENT_ID = a.admission_id
left join departures d
on d.admission_id = a.admission_id
where d.departure_date is null

./hb