Nested subquery making me cry

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)

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 Received on Thu Nov 23 2000 - 04:14:50 CET