Nested subquery making me cry
Date: Thu, 23 Nov 2000 03:14:50 GMT
Message-ID: <KY%S5.7669$TE3.407338_at_typhoon2.ba-dsg.net>
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:
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.
(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 Received on Thu Nov 23 2000 - 04:14:50 CET