Link to home
Start Free TrialLog in
Avatar of huangs3
huangs3Flag for Canada

asked on

Oracle SQL: is there XOR condition?

Hi Experts:

    Is there an XOR operator or something like that in Oracle SQL. I tried but couldn't find iit.
    I want to write a SQL statement that can return records with exactly one fild assigned. For example, for the inputs:
*************************************************************
key_table
-------
ID
-------
a      
b      
c      
-------

tableA                    tableB                                  tableC
_________             -----------------                   -----------------
ID    parent                ID      parent                    ID         parent
-------------              -----------------                   -----------------
1         a                     2           a                         3              b
4         b
*******************************************************************************

I want to return
********************************************************************
ID           IDA       IDB           IDC
-----------------------------------------
a             1          null            null
a            null          2             null
b            null        null             3
b             4          null            null
**********************************************************************

Thank you!
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sean Stuber
Sean Stuber

try something like this...
SELECT *
FROM (SELECT k.id, ta.id ida, NULL idb, NULL idc
      FROM key_table k, tablea ta
      WHERE k.id = ta.parent
      UNION ALL
      SELECT k.id, NULL, tb.id, NULL
      FROM key_table k, tableb tb
      WHERE k.id = tb.parent
      UNION ALL
      SELECT k.id, NULL, NULL, tc.id
      FROM key_table k, tablec tc
      WHERE k.id = tc.parent)
ORDER BY id, COALESCE(ida, idb, idc)

Open in new window