(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(

Answer

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;

Advertisement

Answer