Re: Nested subquery making me cry

Hi,

The queries you have received as solutions should work. The problem that you are getting of "patient.patient_id is always called "invalid column name"" in the query you have written is because you are not selecting from patient in the inner query. Apart from that when you are using nested queries, your outer query should have a where clause.

Hope this helps you in your future queries...

Aparna

Miguel Cruz <mnc_at_admin.u.nu> wrote in message news: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:
>
> 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
Received on Sun Nov 26 2000 - 16:40:45 CET