Julien Julien - 7 months ago 13
SQL Question

SQL : error with an inner join, probably sthg simple

I have 2 tables with the same columns (id, country_code), example :

Table A
--------
id country_code
1 fr
2 fr
3 fr

Table B
--------
id country_code
1 ua
2 fr
3 uk


I would like to get all fields in B where the country_code is different of the one in A for each same id,

Example expected :

id country_code
1 ua
3 uk


I tried with inner join but without any success, any idea?

Here is the error I get :

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '<>'


But in Workbench those fields and tables havee the same collation (set as "Table default"), it's strange..

Answer : I've checked all colations by doing

show table status;


and I updated the Collation column, now it works okay.

Answer

You need to use Collate keyword to change collation:

select b.id,b.country_code
from b join a
on b.id = a.id and b.country_code <> a.country_code collate utf8_unicode_ci;

For more information about collation: What does character set and collation mean exactly?

Comments