CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
hatem
hatem

Reputation: 373

select common rows and non-common rows between 2 tables

I have 2 tables with names kcc_201 and Kcc_300.

table kcc_201 looks like this

ClientMarketCode | WorkDate   | OverDraft
123              | 2012-12-12 | 34.5
456              | 2012-12-12 | 60
98               | 2012-12-12 | 80

table kcc_300 looks like this

ClientMarketCode | WorkDate   | DebitAmount
123              | 2012-12-12 | 80
456              | 2012-12-12 | 90
966              | 2012-12-12 | 100
100              | 2012-12-12 | 787

My question is what is the query that gets me all clients rows having the same ClientMarketCode and workdate matches between 2 tables and retrive also non matched rows from 2 tables without using union

What query get me result like this:

clientMarketcode | WorkDate   | OverDraft | DebitAmount
123              | 2012-12-12 | 34.5      | 80
456              | 2012-12-12 | 60        | 90
98               | 2012-12-12 | 80        | null
966              | 2012-12-12 | null      | 100
100              | 2012-12-12 | null      | 787

Upvotes: 2

Views: 2573

Answers (3)

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10063

Simple, Use FULL OUTER JOIN,

select nullif(k1.ClientMarketCode, k2.ClientMarketCode) as ClientMarketCode,
       nullif(k1.WorkDate, k2.WorkDate) as WorkDate,
       k1.OverDraft,
       k2.DebitAmount
from kcc_201 k1 full outer join kcc_300 k2 
on k1.ClientMarketCode = k2.ClientMarketCode
and k1.WorkDate = k2.WorkDate;

Upvotes: 0

Taryn
Taryn

Reputation: 247690

You can use a FULL OUTER JOIN similar to this:

select 
    coalesce(k2.ClientMarketCode, k3.ClientMarketCode) ClientMarketCode,
    coalesce(k2.WorkDate, k3.WorkDate) WorkDate,
    k2.OverDraft,
    k3.DebitAmount
from kcc_201 k2
full outer join kcc_300 k3
  on k2.ClientMarketCode = k3.ClientMarketCode
  and k2.WorkDate = k3.WorkDate;

See SQL Fiddle with Demo

The result is:

| CLIENTMARKETCODE |   WORKDATE | OVERDRAFT | DEBITAMOUNT |
------------------------------------------------------------
|              123 | 2012-12-12 |        34 |          80 |
|              456 | 2012-12-12 |        60 |          90 |
|               98 | 2012-12-12 |        80 |      (null) |
|              966 | 2012-12-12 |    (null) |         100 |
|              100 | 2012-12-12 |    (null) |         787 |

Upvotes: 4

valex
valex

Reputation: 24144

select 

isnull(kcc_201.clientMarketcode,kcc_300.clientMarketcode),
isnull(kcc_201.WorkDate,kcc_300.WorkDate),
kcc_201.OverDraft,
kcc_300.DebitAmount


from  kcc_201
FULL JOIN kcc_300 on kcc_201.clientMarketcode=kcc_300.clientMarketcode
                     and kcc_201.WorkDate=kcc_300.WorkDate

Upvotes: 0

Related Questions