MySQL-query is not working with NOT IN

Question

I am writing a query that not work correctly

My query:

SELECT * 
FROM admin_marker 
WHERE admin_marker.city NOT IN (SELECT target FROM messsage)

It says

#1267 - Illegal mix of collations
(utf8_general_ci,IMPLICIT) and
(utf8_unicode_ci,IMPLICIT) for operation '='

Check the collation type of each table, and make sure that they have the same collation. http://stackoverflow.com/a/5747047/2899618 — Narendrasingh Sisodia, Jul 10 '15 at 05:51

score 16 · Accepted Answer · edited Mar 30 '16 at 18:46

16

The problem you are facing is due to incompatible collations between the two tables. One way to come around it is to use COLLATE clause in your query:

SELECT * 
FROM admin_marker 
WHERE admin_marker.city NOT IN (SELECT target COLLATE utf8_general_ci 
                                FROM messsage)

Demo here

edited Mar 30 '16 at 18:46

gsamaras

71,951
46
188
305

answered Jul 10 '15 at 05:58

Giorgos Betsos

71,379
9
63
98

score 3 · Answer 2 · answered Jul 10 '15 at 05:54

This is generally caused by comparing two strings of incompatible collation or by attempting to select data of different collation into a combined column. The clause COLLATE allows you to specify the collation used in the query.

Or you can ALTER TABLE to match the COLLATE

score 3 · Answer 3 · answered Jul 10 '15 at 06:12

problem is in the collation between two tables , so please try COLLATE for this , may be this is resolve by the Help of COLLATE easily .

SELECT * FROM admin_marker WHERE admin_marker.city NOT IN (SELECT target COLLATE utf8_general_ci FROM messsage)

and also check that the data base of that is same

incompatible collation or by attempting to select data of different collation into a combined column. The clause COLLATE allows you to specify the collation used in the query.

MySQL-query is not working with NOT IN

3 Answers3