Exclusive selection

kromo · #1 (**permalink**) 07-15-04, 19:45

Maybe it is really simple but right now it's pretty late and I don't have a clue:
Basicly I have two tables with two columns (first one is numeric)

Table A
1:A
2:A
3:B
5:NULL
7:NULL
8:C

Table B
1:A
2:NULL
3:F
5:F
7:NULL
8:NULL

The result should be:

Result Table
1:A
2:A
3:NULL
5:F
7:NULL
8:C

I tried a variaty of joins, subselects and whatever, but failed.
I would appreciate any help.

Kindest regards,
kromo

r937 · #2 (**permalink**) 07-15-04, 22:34

you will have to explain what you want

the results do not give a clue

for example, how do you get NULL from 3:B and 3:F ????

what are you trying to do?

Littlefoot · #3 (**permalink**) 07-16-04, 03:01

Suppose your tables are created as
CREATE TABLE taba (rb number, val varchar2(1));
CREATE TABLE tabb (rb number, val varchar2(1));
and populated as in your example.

Would this do the job?

Code:

SELECT rb, MAX(result)
FROM (
  SELECT 
    a.rb, 
    DECODE(a.val, b.val, b.val, NULL, DECODE(b.val, NULL, NULL, b.val)) result
  FROM TABA a, TABB b
  WHERE a.rb = b.rb
UNION
  SELECT 
    b.rb, 
    DECODE(b.val, a.val, a.val, NULL, DECODE(a.val, NULL, NULL, a.val)) result
  FROM TABA a, TABB b
  WHERE a.rb = b.rb
) 
GROUP BY rb
;

kromo · #4 (**permalink**) 07-16-04, 03:15

Sorry I didn't explain it further. Say the first column is named ID and the second VALUE.
The result should be for (A.ID = B.ID) and sort of XOR for VALUES.

IF (A.VALUE = B.VALUE)
A.VALUE [OR B.VALUE, it doesn't matter]

IF ( (A.VALUE IS NOT NULL) AND (B.VALUE IS NULL) )
A.VALUE

IF ( (A.VALUE IS NULL) AND (B.VALUE IS NOT NULL) )
B.VALUE

IF ( (A.VALUE IS NOTNULL) AND (B.VALUE IS NOT NULL) AND (A.VALUE <> B.VALUE))
NULL

I am working with Oracle 9.2 (right now), if there is a special thing for Oracle I'll take it, if there is an general solution I would prefer that one.

Thank you.

Kindest regards,
kromo