Re: Nested subquery making me cry

Thanks - both of those work.

What I neglected to mention was that I actually needed all the patients, regardless of whether or not any objects had been found in their stomachs.

This is proving tricky. In your first example, no amount of outer joining has come up with the results I'm after. In the second, I run into a brick wall sooner, because "ORA-01799: a column may not be outer-joined to a subquery".

I can think of several approaches I'd try in MS-SQL Server (using subqueries with 'top', or explicit 'outer join on') that don't seem to be accomodated by Oracle's syntax, which is frustrating. It seems strange that similar things wouldn't be possible here.

Again, thanks for your help.

miguel

Conan <conan_at___no-spam__dublin.com> wrote:
>Hi Miguel
>there are a couple of diffent ways to do this, try this one
>
>select p.name,
> o.stomach_object_id,
> o.date_found,
> o.description,
>from patient p,
> stomach_object o,
> (select patient_id,max(date_found) d1 from stomach_object group by
>patient_id) inline
>where p.patient_id=o.patient_id
> and (o.patient_id = inline.patientid and o.date_found = inline.d1)
>
>if you wish to do it using a corealated subquery yuo would do this
>
>select p.name,
> o.stomach_object_id,
> o.date_found,
> o.description,
>from patient p,
> stomach_object o
>where p.patient_id=o.patient_id
> and o.date_found = (select max(date_found) stomach_objects where
>patient_id = o.patient_id)
>
>But I suspect that the first syntax will rin quicker
>
>HTH
>
>Conan
>
>
>
>
Received on Sat Nov 25 2000 - 04:33:32 CET