'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 '='

mysqlmysql-error-1267


Solution 1:[1]

Try putting the column name in backquotes, like st.name. See the docs.

Solution 2:[2]

You should use same collation and charset to all table and columns. If you dont know what collation to use, use utf8_general_ci and utf8 charset. 

ALTER TABLE seo_surgery_city CONVERT TO CHARACTER SET utf8 COLLATE 'utf8_general_ci';
ALTER TABLE seo_surgery_key CONVERT TO CHARACTER SET utf8 COLLATE 'utf8_general_ci';
ALTER TABLE states CONVERT TO CHARACTER SET utf8 COLLATE 'utf8_general_ci';

Solution 3:[3]

Update 2022:

The keyword "name" is now listed on the "keywords and reserved words" list of the official MySQL documentation. You can find all the reserved words there.

Reference: https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-N

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 dj_segfault
Solution 2
Solution 3 NKol

Related Questions