Re: subqueries in from clause

hmm, Sten, what do you do in line 5? does Employment have a column named CID also? If not, you should put SELF.CID if you want to query that, but perhaps you could specify more precisely, what you want to query, because without knowing that, it is difficult to know if that should work or not! @murf:
well, it is possible to use the same table twice, and then you have to rename one of them. it surely only works in a subquery.

Murf <smurphy_at_triad.rr.com> schrieb in im Newsbeitrag: 92do6m$vlm$1_at_nnrp1.deja.com...
> Your "timing" is off with your naming. In the 6th line, you refer to
> COMPANY, which is also referred to in the first line, but renamed SELF.
> Can you be a little more clear with your table-naming? Because it's
> just not going to work with your example having two tables with the
> same name.
>
> Or are you trying to refer to two tables with the same name in
> different databases??
> -Murf
>
> In article <3A3F7F90.9572D22F_at_inf.tu-dresden.de>,
> Sten Loecher <sl13_at_inf.tu-dresden.de> wrote:
> > Hi all,
> >
> > I tried to make a query like this:
> >
> > select * from COMPANY SELF
> > where exists
> > (select PID from
> > (select PID from PERSON where PID in
> > (select PID from EMPLOYMENT where CID in
> > (select CID from COMPANY where CID = SELF.CID))))
> >
> > (PID and CID are just some columns of the tables PERSON and COMPANY
> > respectivly, EMPLOYMENT is an association table).
> >
> > Even if it might seem a little bit crazy to write a query like that, I
> > really need to do it that way. So,
> > I got the following error:
> >
> > (select CID from COMPANY where CID = SELF.CID))))
> >
> > *
> > ERROR at line 6:
> > ORA-00904: invalid column name
> >
> > Does anyone know about restrictions in subqueries performed in the
 from
> > clause of some query. I could not find any Information in the
> > documentation.
> >
> > Thanks for any help,
> >
> > Sten
> >
> > sl13_at_inf.tu-dresden.de
> >
> >
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Thu Dec 28 2000 - 06:11:09 CST