CODEWITHSUNDEEP
mysqlsqlsql-serverjoininner-join
Jeya Suriya Muthumari
Jeya Suriya Muthumari

Reputation: 2021

Compare tables in MSSQL and get different values

I have two tables tmp_1 and tmp_2 here, Table1

------+---------
|name |lastname|
------+---------
|John |rosy   |
------+---------
|Demy |Sanches |
------+---------

Table2

------+----------
|name2|lastname2|
------+----------
|John |rose     |
------+----------
|Demy |Sanches  |
------+----------

I am using Inner join to compare these two tables like,

select * from tmp_1 t1
inner join tmp_2 t2
on t1.name = t2.name
and t1.lastname = t2.lastname

OUTPUT:

name    lastname    name    lastname
Demy    Sanches     Demy    Sanches

Expected Output:

name    lastname    name    lastname
John    rosy        John    rose

which results in the values those are equal. But I would like to get values those differs from two tables. I have searched some stuff in StackOverflow. But I could not get this done, Because of time constraints, I posted the question here to get help from you.

Any help would be more useful to me. Thank you

Upvotes: 0

Views: 36

Answers (3)

Peter Abolins
Peter Abolins

Reputation: 1539

If you simply want all records which don't have an exact match in the two tables, then the following will work. The main issue is that your question is not specific enough. However, logically, the following answer is correct, because it provides you with the complement of your original query (cf De Morgan).

SELECT * FROM tmp_1 t1
    INNER JOIN tmp_2 t2 
        ON (t1.name != t2.name2 OR t1.lastname != t2.lastname2);

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

To find rows where the tables differ in just one column you can write the following

select * from tmp_1 t1
inner join tmp_2 t2 on (t1.name = t2.name2 and t1.lastname != t2.lastname2) or 
                       (t1.name != t2.name2 and t1.lastname = t2.lastname2)

Upvotes: 2

A.D.
A.D.

Reputation: 2372

Use the following may this works.This will work only for lastname mismatch.

select t1.name,t1.lastname,t2.name,t2.lastname from tmp_1 t1
inner join tmp_2 t2
on t1.name = t2.name
and t1.lastname != t2.lastname

If there a conditional selection with both field then 

select t1.name,t1.lastname,t2.name,t2.lastname from tmp_1 t1
inner join tmp_2 t2
on ((t1.lastname != t2.lastname and t1.name = t2.name) or 
(t1.lastname  = t2.lastname and t1.name != t2.name))

Upvotes: 1

Related Questions