Re: Nested subquery making me cry

Miguel Cruz wrote in message ...
>
>You'd think this would be very easy. Let's say I have a table of patients,
>and another table of the objects found in their stomachs:
>
>patient:
> patient_id
> name
>
>stomach_object:
> stomach_object_id
> patient_id
> date_found
> description
>
>And I'd like to list all the patients along with the MOST RECENT object
>found in each one's stomach.
>
>I've tried variations on:
>
> select patient.name,
> (select *
> from
> (select description
> from stomach_object
> where stomach_object.patient_id=patient.patient_id
> order by date_found desc)
> where rownum=1)
> from patient;
>
>but the patient.patient_id is always called "invalid column name".
>
>My problem seems to be that patient is valid in the direct subquery, but
 has
>disappeared from the scope of the inner subquery. How can I get it down
>there? Or is there another way to structure this? I've tried all manner of
>grouping nonsense and been unable to conceive of a way to do it without the
>nesting.
>
>Thanks very much for any ideas.
>
>miguel

You could try something like....

select P.patient_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)
                                         from    stomach_object
                                         where  patient_id = O.patient_id)