Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: subselect SQL*plus

Re: subselect SQL*plus

From: Tony <tonys_at_lords.com>
Date: 1998/03/09
Message-ID: <35033826.4F94@lords.com>#1/1

tzadkiel wrote:
>
> The answer they are probably looking for is:
>
> select flight, time_of_departure, time_of_arrival
> from table1
> where departing_from_code = 'CDG'
> and arrives_at_code in (select code
> from citycode
> where city = 'LONDON')

This is only a note to tzadkiel. Without knowing anything about the original problem I would in the real life write:

select flight, time_of_departure, time_of_arrival  from table1
 where departing_from_code = 'CDG'
   and exists (select code

                         from citycode
                         where code = arrives_at_code and city =
'LONDON') and probably end up with a much much faster answer. The server it self is still not capable to "translate" the human way of thinking as in your example into the computer way of doing it fast unless you ask for CBO with all the ANALYZE work.

:-) Tony.  

>
> share and enjoy
>
> Roger Langedal <roger.langedal_at_bergen.mail.telia.com> wrote in article
> <3501fd2b.0_at_d2o202.telia.com>...
> > I`trying to learn SQL, but I`m stuck at the last example I tried to
 solve.
> >
> > This is the case.....
> >
> > I have a table FLIGHTSCEDULE containing the columnnames:
> >
> >
> > This is what the tables look like:
> >
> > TABLE1->>
> >
> > FLIGHT DEPARTING_FROM_CODE
> > ARRIVES_AT_CODE TIME_OF_DEPATURE TIME_OF_ARRIVAL
> > e.g: SA231 FBU
> > HTR 0900
> > 1000
> > BA111 CDG
> > GTW 1320
> > 1430
> >
> >
> >
> >
> >
> > TABLE2:
> > CONTAINS DATA IDENTIFYING THE TO AND FROM CODES...
> >
> > Table CITYCODE:
> > CODE
 CITY
> >
> > e.g: HTR LONDON
> > GTW
 LONDON
> > CDG
 PARIS
> >
> >
> > I`m supposed to use a subquerie too list the flights from Paris to
> > London....
> >
> > As there are TO instances of London, I am not able find a way to select
 and
> > return BOTH CDG(paris) - HTR(london) AND CDG(london)??!!
> >
> > I hope ther is anybody out there who takes the time too look at this....
> >
> > Roger
> >
> >
> >
> >
Received on Mon Mar 09 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US