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