CODEWITHSUNDEEP
mysql
pal3
pal3

Reputation: 241

Mysql compare two tables and display unmatch

I have the following two tables:

test1

+----+------+-----+
| id | name | age |
+----+------+-----+
| 1  | john | 23  |
| 2  | jane | 22  |
+----+------+-----+

test2

+----+------+-----+
| id | name | age |
+----+------+-----+
| 1  | john | 23  |
| 2  | jane | 21  |
+----+------+-----+

I want to display only id number 2 because the age did not match from table test1

Sample Output:

+----+------+-----+
| id | name | age |
| 2  | jane | 21  |
+----+------+-----+

This is my current attempt at a query:

SELECT * FROM test1 WHERE age NOT IN (SELECT age FROM test2)

Upvotes: 2

Views: 86

Answers (3)

dchar
dchar

Reputation: 1695

This should get you the results you want

SELECT test1.id as id1, test2.id as id2, test1.name, test1.age as age1, test2.age as age2
FROM test1,test2
WHERE test1.name = test2.name 
AND test1.age != test2.age

Upvotes: 1

Nilesh Thakkar
Nilesh Thakkar

Reputation: 1459

You can use INNER JOIN, This may help you.

SELECT test1.* FROM test1 INNER JOIN test2 ON test1.name = test2.name 
AND test1.age <> test2.age

Working sql fiddle for your reference

Upvotes: 2

Rahul
Rahul

Reputation: 77866

You can use WHERE NOT EXISTS like this:

SELECT t.* 
FROM test1 t
WHERE NOT EXISTS (SELECT 1 FROM test2 WHERE age <> t.age)

Upvotes: 1

Related Questions