SQL : error with an inner join, probably sthg simple

Question

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.

Show us the inner join(s) you tried! – jarlh Apr 25 '16 at 12:33 — jarlh, Apr 25 '16 at 12:33

score 4 · Answer 1 · answered Apr 25 '16 at 12:34

4

You can use join:

select b.*
from b join
     a
     on b.id = a.id and b.country_code <> a.country_code;

answered Apr 25 '16 at 12:34

Gordon Linoff

thank you but I get : "Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '<>'", any idea? – Julien Apr 25 '16 at 12:39

score 2 · Accepted Answer · edited May 23 '17 at 12:31

2

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;

edited May 23 '17 at 12:31

Community

answered Apr 25 '16 at 12:42

Haytem BrB

it works but why it doesn't work without this collate added in the query? – Julien Apr 25 '16 at 12:46
Because you two different collations in table a and table b, in other terms it's like b.country_code and a.country_code are in different languages, so you need to translate one of them to the other one's language. To better understand this check the link in my answer. – Haytem BrB Apr 25 '16 at 12:48
but in Workbench each fields and also table use the same collation (Collation= "Table default"), it's strange.. – Julien Apr 25 '16 at 12:53
well you need to specify collation instead of using default values (Table default is not a collation), default values can be different between tables. – Haytem BrB Apr 25 '16 at 12:55
1

you were right, I checked all collations with show table status; and I saw different collations, thanks! – Julien Apr 25 '16 at 13:07

score 0 · Answer 3 · answered Apr 25 '16 at 12:39

0

select b.id,b.country_code
from b 
left join a on b.country_code = a.country_code
where a.country_code is null

answered Apr 25 '16 at 12:39

Ankit Agrawal

Although this code may answer the question, providing additional context regarding _why_ and/or _how_ it answers the question would significantly improve its long-term value. Please [edit] your answer to add some explanation. – Toby Speight Apr 25 '16 at 13:47

3 Answers3