:)

Try this: (Added "LEFT")

select a.admit_date, a.ADMISSION_ID,
a.ADMIT_ORDINAL, d.departure_date

from patient_admissions a LEFT join departures d
on d.admission_id = a.ADMISSION_ID

where d.departure_date is null

If it works, add the patients table.
This is a way to know if record exists in table A,
but it doen't exist in table D.

If for some reason, it doesn't work. A Stored Procedure
is a good choise. Although I prefere it to be a SQL statement.

Juan Jose

--- In firebird-support@yahoogroups.com, "Kevin Stanton"
<Kevin.Stanton@R...> wrote:

> Have you considered a stored proc to return your result set?
>
> That way you can have multiple lookups.
>
> Kevin
>
>
>
>
>
> _____
>
> From: Clay Shannon [mailto:cshannon@d...]
> Sent: Monday, March 28, 2005 1:23 PM
> To: firebird-support@yahoogroups.com
> Subject: RE: [firebird-support] Re: Calling all SQL gurus
>
>
>
> JJ Ochoa suggested:
>
> << 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>>
>
> Thanks, Juan Jose,
>
> Unfortunately that doesn't work, either, because there's no record

at all in

> the Departures table until they depart. Your select statement is
> syntactically fine, but returns no records.
>
> Another way of looking at what I need is something like this:
>
> Select <whatever>
> >From <blab la>
> Where count(<Patient_admissions column>) > count(<Departure

column>)

>
> Clay Shannon,
> Dimension 4 Software
>
>
>
>
>
>
> Yahoo! Groups Sponsor
>
>
>
> ADVERTISEMENT
>
>

<http://us.ard.yahoo.com/SIG=129atlbvj/M=298184.6018725.7038619.30011
76/D=gr

>

oups/S=1705115386:HM/EXP=1112131393/A=2593423/R=0/SIG=11el9gslf/*http
:/www.n

> etflix.com/Default?mqso=60190075> click here
>
>
>
> <http://us.adserver.yahoo.com/l?

M=298184.6018725.7038619.3001176/D=groups/S=

> :HM/A=2593423/rand=525366690>
>
>
>
> _____
>
> Yahoo! Groups Links
>
> * To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
> * To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
> <mailto:firebird-support-unsubscribe@yahoogroups.com?

subject=Unsubscribe>

>
> * Your use of Yahoo! Groups is subject to the Yahoo!
> <http://docs.yahoo.com/info/terms/> Terms of Service.
>
>
>
> [Non-text portions of this message have been removed]