CODEWITHSUNDEEP
mysql
Sanghamitra Lahiri
Sanghamitra Lahiri

Reputation: 321

Compare two database table

I have two database tables, tmp1 and tmp2

tmp1:

+----+------+---------+---------+
| id | name | add1    | add2    |
+----+------+---------+---------+
| 1  | NULL | NULL    | NULL    |
| 2  | NULL | NULL    | NULL    |
| 3  | mum1 | rajpur1 | rajpur2 |
| 4  | mum3 | rajpur3 | rajpur4 |
| 5  | mum4 | rajpur4 | rajpur5 |
+----+------+---------+---------+

tmp2:

+----+------+---------+---------+
| id | name | add1    | add2    |
+----+------+---------+---------+
| 1  | NULL | NULL    | NULL    |
| 2  | mum  | rajpur  | rajpur1 |
| 3  | NULL | NULL    | NULL    |
| 4  | mum2 | rajpur2 | rajpur3 |
| 5  | mum4 | rajpur4 | rajpur5 |
+----+------+---------+---------+


select a.id 
from tmp1 a 
inner join tmp2 b 
where a.id = b.id 
  and (a.name is NULL or a.add1 is NULL or a.add2 is NULL) 
   or (a.name!=b.name or a.add1!=b.add1 or a.add2!=b.add2);

Here I have tried the above code to show the following result:

+----+
| id |
+----+
| 1  |
| 2  |
| 3  |
| 4  | 
+----+

But it doesn't show me the desired result.

Here i want to print the first table's id where one or many other row's values are either null or one or many other row's values are unmatched with tmp2's those rows values.

Upvotes: 1

Views: 76

Answers (2)

Rajat
Rajat

Reputation: 5803

You were missing the ON. And as The Impaler astutely pointed out, the columns in the temp2 also need to be checked for NULL. 

select a.id 
from tmp1 a 
inner join tmp2 b on a.id=b.id
where coalesce(a.name,a.add1,a.add2,b.name,b.add1,b.add2) is null or
      coalesce(a.name,a.add1,a.add2)<>coalesce(b.name,b.add1,b.add2));

Upvotes: 1

The Impaler
The Impaler

Reputation: 48770

You are missing the ON clause that specifies how to join the table(s). Also, your search condition is incomplete.

select a.id
from tmp1 a
join tmp1 b on a.id = b.id
where a.name is null or a.add1 is null or a.add2 is null
   or b.name is null or b.add1 is null or b.add2 is null
   or (a.name <> b.name or a.add1 <> b.add1 or a.add2 <> b.add2)

Upvotes: 1

Related Questions