Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    13

    Unanswered: Exclusive selection

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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
    ;

  4. #4
    Join Date
    Dec 2003
    Posts
    13
    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

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select ta.id, (CASE WHEN ta.value = tb.value THEN ...)
    from tableA ta
    INNER JOIN
    tableB tb ON
    ta.id = tb.id
    Last edited by r123456; 07-16-04 at 05:12.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Dec 2003
    Posts
    13
    Thank you all very much.

    I tried the "CASE" approach and it worked like a charm.

    You saved my day.


    Kindest regards,
    kromo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •