CODEWITHSUNDEEP
mysqlsqljoin
Juno J
Juno J

Reputation: 197

(challenging sql)compare two tables for different value

I want to compare values of table a & c and find differences. We can specify unique_key of table a by compare id between table a & b, and by matching unique_key of table b & c, we can compare Name of table a & c.

Can we get the list of rows that Name has different value for table a & c?

So If I write table,

table a 
+----+------+-----------+
| id | Name | int_value |
+----+------+-----------+
| a1 | aa   |         1 |
| a2 | bb   |         2 |
| a3 | cc   |         5 |
+----+------+-----------+
table b
+----+------+-----------+
| id | Name |unique_key |
+----+------+-----------+
| a1 | !!   |        u1 |
| a2 | @@   |        u2 |
| a3 | ##   |        u3 |
+----+------+-----------+
table c 
+----+------+-----------+
| id | Name |unique_key |
+----+------+-----------+
| c1 | aa   |        u1 |
| c2 | B1   |        u2 |
| c3 | C1   |        u3 |
+----+------+-----------+

As a result, I want to get

NameA NameC unique_key
bb    B1    u2
cc    C1    u3

I tried this sql, but none of them get me correct result. What would you do?

SELECT a.Name, c.Name, b.unique_key
FROM a
INNER JOIN b
ON a.id = b.id
LEFT JOIN c
ON b.unique_key = c.unique_key
and NOT EXISTS (SELECT * FROM a WHERE a.Name = b.Name);

real sql(for real DB) I made

SELECT i.*, g.*, r.g_id
FROM i
INNER JOIN r
ON i.id = r.id
LEFT JOIN g
ON r.g_id = g.g_id
WHERE CAST('i.name1' as varbinary) != CAST('g.name1' as varbinary)
OR CAST('i.name2' as varbinary) != CAST('g.name2' as varbinary)
OR CAST('i.name3' as varbinary) != CAST('g.name3' as varbinary)
GROUP BY i.id
LIMIT 30;

error I got:

Error occurred.SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varbinary) != CAST(

Upvotes: 1

Views: 74

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You seem to just want some joins and a comparison:

select a.name as nameA, c.name as nameC, b.unique_key
from a join
     b
     on a.id = b.id join
     c
     on b.unique_key = c.unique_key
where a.name <> c.name;

I don't see why a binary comparison would be needed for columns called name. But you can use the binary keyword if that is really needed (it is not needed for your sample data).

Also, your question is specifically about names being different -- not about missing rows. So, outer joins don't seem appropriate either.

Upvotes: 1

Hesam Akbari
Hesam Akbari

Reputation: 1151

You can cast the field as BINARY before comparing

by default aa and AA, both are the same

SELECT i.*, g.*, r.g_id
FROM i
INNER JOIN r
ON i.id = r.id
LEFT JOIN g
ON r.g_id = g.g_id
WHERE CONVERT('i.name1' , BINARY) != CONVERT('g.name1' , BINARY)
OR CONVERT('i.name2' , BINARY) != CONVERT('g.name2' , BINARY)
OR CONVERT('i.name3', BINARY) != CONVERT('g.name3', BINARY)
GROUP BY i.id
LIMIT 30;

Upvotes: 2

Related Questions