CODEWITHSUNDEEP
sqlsql-servert-sqljoinunion
John Bristiw
John Bristiw

Reputation: 27

Select union distinct records from two tables on the basis of common column

I want to union Table1 and Table2 and select distinct records. If the Transaction_id is same in both the tables, I want the record from Table1 (not from Table2 at all). Could someone please write a SQL Server query for me? I am trying below query for this but I am getting duplicate records.

Select * from Table1
union
Select * from Table2

Table1

Transaction_id  Product Quantity    Return
     1           Pen       2       No DATA
     2           pencil    4       No DATA
     3           sharpner  6       No DATA
     4           eraser    10      No DATA

Table2 

Transaction_id  Product Quantity    Return
     3           sharpner   6       Yes
     5           Book       9       Yes

Union Table

Transaction_id  Product Quantity    Return
     1           Pen       2       No DATA
     2           pencil    4       No DATA
     3           sharpner  6       No DATA
     4           eraser    10      No DATA
     5           Book      9       Yes

Upvotes: 1

Views: 2623

Answers (2)

GMB
GMB

Reputation: 222462

I would use a full join and conditional logic here:

select
    coalesce(t1.transaction_id, t2.transaction_id)transaction_id,
    case when t1.transaction_id is not null then t1.product else t2.product end product,
    case when t1.transaction_id is not null then t1.quantity else t2.quantity end quantity,
    case when t1.transaction_id is not null then t1.return else t2.return end return
from table1 t1
full join table2 t2 on t1.transaction_id = t2.transaction_id

Note that return is a language keyword, hence not a good choice for a column name.

Upvotes: 0

zealous
zealous

Reputation: 7503

Try the following and here is the demo.

select
  transaction_id,
  product,
  quantity,
  retur
from table1

union all

select
  transaction_id,
  product,
  quantity,
  retur
from table2 t2
where not exists (
  select
    transaction_id
  from table1 t1
  where t2.transaction_id = t1.transaction_id
)

Output:

*------------------------------------------*
|transaction_id  product quantity   retur  |
*------------------------------------------*
|  1               Pen        2     No DATA|
|  2               pencil     4     No DATA|
|  3               sharpner   6     No DATA|
|  4               eraser     10    No DATA|
|  5               book       9      yes   |
*------------------------------------------*

Upvotes: 1

Related Questions