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

Home -> Community -> Usenet -> c.d.o.tools -> Re: Query problem

Re: Query problem

From: Bastiaan Schaap <bschaap_at_desyde.nl>
Date: 2000/08/11
Message-ID: <newscache$b654zf$js$1@lnews.actcom.co.il>#1/1

Thanx Joanna,

I actually found this solution just before you posted it. However I was surprised it worked. Actually I expected to find double rows.. Does the union automatically do a kind of select distinct for you? Is this why oracle has the 'union all' ?

Anyway, thanx again,

Bastiaan Schaap
Oracle web development,
Desyde BV - Baarn
http://www.desyde.nl/
tel. +31355411711

Joanna C <joannac_at_peregrine.ie> wrote in message news:8mtvd4$hf4$1_at_kermit.esat.net...
> try this..
>
> SELECT A.ID as old_id, B.ID as new_id FROM A, B WHERE a.id(+) = b.id
> union
> SELECT A.ID as old_id, B.ID as new_id FROM A, B WHERE a.id = b.id (+)
>
>
> "Bastiaan Schaap" <bschaap_at_desyde.nl> wrote in message
> news:newscache$6682zf$cs$1_at_lnews.actcom.co.il...
> > Hi all!
> >
> > I have a little query problem which I find very difficult to solve, I'm
 more
> > of a plsql man ;-)
> >
> > Here it is: Say I have to tables A and B both with a column e.g. ID. Now
 I
> > want to compare these tables. In order to do that I want to do a select
 that
> > joins these two tables, and creates a result for every row in A and
 every
> > row in B. Ergo: if A.ID exists and B.ID doesn't, I want to see a row for
> > it. Same is for B, if B.ID exists but A.ID doesn't I also want to see a
 row
> > in the result set. I want it to be one statement... The 'truth table'
 for
> > this would be :
> > A: B: row in resultset:
> > 0 0 0 (does not occur, but I'll give it anyway..)
> > 1 0 1
> > 0 1 1
> > 1 1 1
> >
> > Where a zero indicates that no row was found for the ID.
> >
> > Actually theoretically this works:
> > SELECT A.ID as old_id, B.ID as new_id FROM A, B WHERE a.id(+) = b.id(+);
> >
> > However Oracle doesn't support this. Does anyone know a solution for
 this
> > (little?) problem??
> >
> > Thanx,
> >
> > Bastiaan Schaap
> > Oracle web development,
> > Desyde BV - Baarn
> > http://www.desyde.nl/
> > tel. +31355411711
> >
> >
> >
> >
>
>
Received on Fri Aug 11 2000 - 00:00:00 CDT

Original text of this message

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