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

Answer 2

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

Answer 3

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.