Search code examples
mysqlmysql-error-1267

Query with column named "name"

I am trying to join a few tables but it is failing on my join statement. I believe it is because the column name in the second join is "name" and perhaps MySQL thinks i am trying to access an attribute? how can i get around this?

SELECT surgery_city.*, s.surgeon_type, st.abbrev
FROM surgery_city 
LEFT JOIN surgery_key as s ON s.surg_id = treatment_id 
LEFT JOIN states as st ON st.name = surgery_city.state
WHERE treatment_id='10001'

The issue is the second left join where i reference st.name - any ideas on how i can reference that column properly? changing the column name in the table is not an option at this point unfortunately :(..

Thanks,

Silver Tiger

UPDATE:

The error I get on the query above is:

[Err] 1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

when i surround the field by back ticks i get the following:

LEFT JOIN states as st ON `st.name` = seo_surgery_city.state

I get the following instead:

[Err] 1054 - Unknown column 'st.name' in 'on clause'

It also fails on 

LEFT JOIN states as st ON st.`name` = seo_surgery_city.state

(single quotes = back ticks there, but it wont display properly here)

[Err] 1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

Also fails on 

LEFT JOIN states as st ON `st`.`name` = seo_surgery_city.state

[Err] 1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

Solution

  • You should embrace the column name with backticks:

    st.`name`

    UPD

    The problem is that the columns have different collations, try the following:

    SELECT surgery_city.*, s.surgeon_type, st.abbrev
FROM surgery_city 
LEFT JOIN surgery_key as s ON s.surg_id = treatment_id 
LEFT JOIN states as st ON st.name = (surgery_city.state COLLATE utf8_unicode_ci)
WHERE treatment_id='10001'

    But to fix this you should update the collation for one of the columns: states.name or seo_surgery_city.state. They should both have utf8_general_ci.