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 '='

Answer 1

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