CODEWITHSUNDEEP
sqloracle-database
user1751356
user1751356

Reputation: 615

Sql compare mismatch values

I would like to compare values between 2 different tables and return only the differences but im struggling to come up with a query.

I have a employee tab 1 and employee tab 2. Im comparing data between dev and test.

employee1

id1 cd1   desc1 
1  reg  regular
2  temp temporary
3  reg  regular
4  new  new hire
5  del  terminated

employee2

id2 cd2   desc2
1  reg  regular
2  temp temporary-456
3  reg  regular-123
4  new  new hire
5  del  terminated

My result set should be (i will make a join on emplid, cd)

id cd1  desc1     cd2  desc2            
1  reg regular    reg  regular-123
2  temp temporary temp temporary-456

I tried this approach 

SELECT Distinct EMPL_ID, cd1, desc1, CD2, desc2
FROM   empl 1,  empl2
WHERE EMPL_ID1= EMPL_ID2
and CD1=cd2
AND desc1<>desc2

But this is giving duplicate results. Thanks for your help!

Upvotes: 0

Views: 70

Answers (3)

Subbu Vaduguri
Subbu Vaduguri

Reputation: 3

Try this way to compare Dev & Test data

SELECT Distinct emp_dev.EMPL_ID, emp_dev.cd, emp_dev.desc, emp_tst.CD, emp_test.desc
FROM   empl1 emp_dev,  empl2 emp_tst
WHERE emp_dev.EMPL_ID= emp_tst.EMPL_ID
and ( emp_dev.CD=emp_tst.cd OR emp_dev.desc<>emp_tst.desc )

Or if you just want to the difference between dev & test :

(SELECT "Record Missing in Test" Note, EMPL_ID, cd, desc 
FROM   empl1 emp_dev 
 MINUS 
 SELECT "Record Missing in Test" Note, EMPL_ID, cd, desc 
 FROM   empl1 emp_tst) 
union 
(SELECT "Record Missing in Dev" Note, EMPL_ID, cd, desc 
FROM   empl1 emp_tst 
 MINUS 
 SELECT "Record Missing in Dev" Note, EMPL_ID, cd, desc 
 FROM   empl1 emp_dev)

Hope this helps

Upvotes: 0

Jack Kada
Jack Kada

Reputation: 25202

Join the tables but use the id column rather then the cd1 column. No need for distinct in your query either

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Based on your data, you need to join on id, not cd1:

SELECT empl1.EMPL_ID, cd1, desc1, CD2, desc2
FROM  empl1 JOIN
      empl2
      ON  empl1.EMPL_ID = empl2.EMPL_ID and empl1.CD1 = empl2.cd2 and
          empl1.desc1 <> empl2.desc2  ;

Upvotes: 1

Related Questions