CODEWITHSUNDEEP
sqloracle-database
fo_x86
fo_x86

Reputation: 2613

Oracle sql union with no duplicate on a single column

In Oracle, is it possible to perform a union where the duplicate condition is on a single column rather than the entire row?

I have table Aand B that have 2 columns: item_name, price. I'd like to create a view that for certain item_names, it looks in table A to see if the item_name is present, and if so use the price in A, if not go to B and use the price in B, then union the rest of item_name in B that have not yet been added to the view.

For example,

 Table A                Table B
 ----------------       ----------------
 item_name price        item_name price
 ----------------       ----------------
 shoe      10           shoe      8
 socks     2            socks     4
 shirt     5            t-shirt   3
 gloves    1            glasses   15
                        pants     7

For shoe and socks I'd like to use table A's prices if available, and if not use table B. So in the end, my view should look like this:

 View
 -----------------------
 item_name price source
 -----------------------       
 shoe      10    A
 socks     2     A
 t-shirt   3     B
 glasses   15    B
 pants     7     B

I tried 

 select * from A a
 where item_name in ('shoe', 'socks')
 union
 select * from B b
 where b.item_name not in 
     (select item_name from A
      where item_name in ('shoe', 'socks'))

Which I don't like because the query select * from A where item_name in ('shoe', 'socks') is duplicated. Is there a better/more efficient way of doing this?

Upvotes: 3

Views: 6600

Answers (3)

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10063

Try this,

    create view viewname as (
    select coalesce(a.item_name, b.item_name) as item_name,
       coalesce(a.price, b.price) as price,
       (case when a.item_name=b.item_name then 'A' else 'B' end) as source
from tablea a right outer join
     tableb b
     on a.item_name = b.item_name)

made slight change Gordon's ans

Upvotes: 0

Serkan Arıkuşu
Serkan Arıkuşu

Reputation: 5609

Since you are using Oracle, I may suggest the following, it would do the trick

select NVL(A.ITEM_NAME,B.ITEM_NAME) AS ITEM_NAME, 
NVL(A.PRICE,B.PRICE) AS PRICE 
FROM A as a RIGHT JOIN B as b ON A.ITEM_NAME=B.ITEM_NAME

To understand why it works, simply try it without NVL, the resulting right join results

A_item  A_price     B_item  B_price
shoe    10          shoe    8
socks   2           socks   4
(null)  (null)      glasses 15
(null)  (null)      t-shirt 3
(null)  (null)      pants   7

Since you do not want the null values from table A, use NVL

NVL has also equivalent functions in mysql/mssql etc

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I think you are looking for a join:

select coalesce(a.item_name, b.item_name) as item_name,
       coalesce(a.price, b.price) as price,
       (case when a.price is not null then 'A' else 'B' end) as source
from a full outer join
     b
     on a.item_name = b.item_name

Upvotes: 8

Related Questions