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. stackoverflow.com/a/5747047/2899618 — Narendrasingh Sisodia
– Narendrasingh Sisodia, Commented Jul 10, 2015 at 5:51

gsamaras · Accepted Answer · 2016-03-30 18:46:57Z

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, 2016 at 18:46

gsamaras

73.7k50 gold badges210 silver badges330 bronze badges

answered Jul 10, 2015 at 5:58

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Techie · Accepted Answer · 2015-07-10 05:54:29Z

3

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

answered Jul 10, 2015 at 5:54

Techie

45.2k45 gold badges164 silver badges248 bronze badges

Comments

Neha Sinha · Accepted Answer · 2015-07-10 06:12:31Z

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.

answered Jul 10, 2015 at 6:12

Neha Sinha

4564 silver badges9 bronze badges

Collectives™ on Stack Overflow

MySQL-query is not working with NOT IN

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related