Query problem

From: Bastiaan Schaap <bschaap_at_desyde.nl>
Date: 2000/08/10
Message-ID: <newscache$6682zf$cs$1@lnews.actcom.co.il>#1/1

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 Thu Aug 10 2000 - 00:00:00 CDT