Query with column named "name"

Question

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

Can you tell us how it's failing? Are you getting an error? What error? — dj_segfault, Jan 17 '12 at 16:06
@MouseFood You sure about that? [MySQL Reserved Words](http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html) — Joe Stefanelli, Jan 17 '12 at 16:09
@MouseFood I didn't see `name` is a keyword in [reference](http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html) — Shiplu Mokaddim, Jan 17 '12 at 16:14

score 1 · Answer 1 · answered Jan 17 '12 at 16:10

1

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

answered Jan 17 '12 at 16:10

dj_segfault

11,957
4
29
37

@Shiplu: Because the OP did not include the error message, I can only infer what the problem is. You can't honestly say "I know the solution to your problem even though you didn't tell me what it is yet". – dj_segfault Jan 17 '12 at 16:20
the suggestion in not correct anyway =), only the column name should be quoted. – newtover Jan 17 '12 at 16:29

newtover · Accepted Answer · 2012-01-17T16:17:57.260

1

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.

edited Jan 17 '12 at 16:17

answered Jan 17 '12 at 16:12

newtover

31,286
11
84
89

adding the collate function worked, no back ticks required .. thank you very much. – Silvertiger Jan 17 '12 at 16:24
1

@Silvertiger, I am glad. But if you can, you should better update the column definition in `st.name` to `utf8_general_ci` =) – newtover Jan 17 '12 at 16:28

Shiplu Mokaddim · Answer 3 · 2012-01-17T16:38:25.957

1

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

edited Jan 17 '12 at 16:38

answered Jan 17 '12 at 16:20

Shiplu Mokaddim

56,364
17
141
187

[SQL] ALTER TABLE seo_surgery_city CONVERT TO CHARACTER SET latin1 COLLATE 'utf8_general_ci'; [Err] 1253 - COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1' – Silvertiger Jan 17 '12 at 16:22

score 0 · Answer 4 · answered Feb 07 '22 at 10:44

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

Query with column named "name"

4 Answers4