Illegal mix of collations error in MySql

Question

Just got this answer from a previous question and it works a treat!

SELECT username, (SUM(rating)/COUNT(*)) as TheAverage, Count(*) as TheCount 
FROM ratings WHERE month='Aug' GROUP BY username HAVING TheCount > 4
ORDER BY TheAverage DESC, TheCount DESC

But when I stick this extra bit in it gives this error:

Documentation #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '='

SELECT username, (SUM(rating)/COUNT(*)) as TheAverage, Count(*) as TheCount FROM 
ratings WHERE month='Aug' 
**AND username IN (SELECT username FROM users WHERE gender =1)**
GROUP BY username HAVING TheCount > 4 ORDER BY TheAverage DESC, TheCount DESC

The table is:

id, username, rating, month

Cheers

Answer 1

Make sure your version of MySQL supports subqueries (4.1+). Next, you could try rewriting your query to something like this:

SELECT ratings.username, (SUM(rating)/COUNT(*)) as TheAverage, Count(*) as TheCount FROM ratings, users 
WHERE ratings.month='Aug' and ratings.username = users.username
AND users.gender = 1
GROUP BY ratings.username
HAVING TheCount > 4 ORDER BY TheAverage DESC, TheCount DESC

Answer 2

HAvING TheCount > 4 AND username IN (SELECT username FROM users WHERE gender=1)

but why i am answering, you dont voted me as right answer :)

Answer 3

• Check that your users.gender column is an INTEGER.
• Try: alter table users convert to character set latin1 collate latin1_swedish_ci;

Answer 4

SELECT  username, AVG(rating) as TheAverage, COUNT(*) as TheCount
FROM    ratings
        WHERE month='Aug'
        AND username COLLATE latin1_general_ci IN
        (
        SELECT  username
        FROM    users
        WHERE   gender = 1
        )
GROUP BY
        username
HAVING
        TheCount > 4
ORDER BY
        TheAverage DESC, TheCount DESC;

Answer 5

1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and

(latin1_general_ci,IMPLICIT) for operation '='

he table is:

id, username, rating, month

Check the collation type of each table, and make sure that they have the same collation.

After that check also the collation type of each table field that you have use in operation.

I had encountered the same error, and that tricks works on me.