0

Following is my query in which I am getting results from two different tables, but its giving me following error kindly let me know what i did wrong:

Error: #1267 - Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '='

SELECT MD5( pre_quiz.qid ),
       pre_quiz.quiz_title,
       pre_quiz.quiz_desc,
       pre_course.cname
FROM pre_quiz
LEFT JOIN pre_course ON 
MD5( pre_course.cid ) = pre_quiz.quiz_course_id
WHERE pre_quiz.createdby =  'user'
ORDER BY pre_quiz.quiz_title
Amarnath Balasubramanian
  • 9,300
  • 8
  • 34
  • 62
user3027531
  • 282
  • 1
  • 5
  • 20
  • 1
    I believe this is caused by `pre_quiz` and `pre_course` having different table collations. Check that they're both `latin1_swedish` or `utf8_general_ci`. It may also be a per-column setting, so check `cid` and `quiz_course_id` for the encoding type. – James Feb 07 '14 at 11:03
  • @James no issues down that road – user3027531 Feb 07 '14 at 11:05
  • @James collation of both tables is the same however collation for field `pre_quiz.quiz_course_id = latin1_swedish` but for `pre_course.cid = BLANK FIELD` – user3027531 Feb 07 '14 at 11:11

1 Answers1

0

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

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

Here's how to check which columns are the wrong collation:

SELECT table_schema, table_name, column_name, character_set_name, collation_name

FROM information_schema.columns

WHERE collation_name = 'latin1_general_ci'

ORDER BY table_schema, table_name,ordinal_position;

And here's the query to fix it:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET latin1 COLLATE 'latin1_swedish_ci';

Edit

To change the collation of a column

ALTER TABLE MyTable ALTER COLUMN Column1 [TYPE] COLLATE [NewCollation]

Source

Amarnath Balasubramanian
  • 9,300
  • 8
  • 34
  • 62
  • Collation of both tables is the same however collation for field `pre_quiz.quiz_course_id = latin1_swedish` but for `pre_course.cid = BLANK FIELD` – user3027531 Feb 07 '14 at 11:12
  • so set the collation for it check this site for setting collation for column http://blog.sqlauthority.com/2008/12/20/sql-server-change-collation-of-database-column-t-sql-script/ it is better to alter the column rather then using collate in single query because you need to use collate in every query – Amarnath Balasubramanian Feb 07 '14 at 11:13
  • All I am trying to do is to get the pre_course.cname value based on MD5( pre_course.cid ) = pre.quiz_course_id . course Id is present for course in pre_quiz but its name present in the other table I am trying to get the name based on the id with left join. Isn't it appropriate way to do so? – user3027531 Feb 07 '14 at 11:21
  • the problem is not with the join , the columns do not have the same collation, i.e is the problem here, so trying chnging it..! – Amarnath Balasubramanian Feb 07 '14 at 11:23
  • Altered both my tables then again ran the query error is still the same :( . – user3027531 Feb 07 '14 at 11:36
  • after altering have you checked the table schema?? – Amarnath Balasubramanian Feb 07 '14 at 14:08