backtick quote causing error in sql query

Question

SELECT `text`,
       `oc_product_to_category.product_id`
FROM `oc_product_attribute` AS pa
LEFT JOIN `oc_attribute_description` AS od ON pa.`attribute_id`=od.`attribute_id`
LEFT JOIN `oc_product_to_category` AS opc ON pa.`product_id`=od.`product_id`

the problem caused by oc_product_to_category.product_id. I removed that and it work. As I know I can do like this opc.product_id but why oc_product_to_category.product_id doesn't work?

see "When to use single quotes, double quotes, and backticks?" http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks — road242, Jul 14 '14 at 08:47
Why are you joining table with alias `opc` using columns from other tables? — shree.pat18, Jul 14 '14 at 08:48
Why are you using backticks on identifiers that are not reserved words and have no spaces? — codenheim, Jul 14 '14 at 08:52

codenheim · Accepted Answer · 2014-07-15T19:34:28.480

Backticks aren't causing your problem in this case.

You aliased the table, but then used the original name in the column list. You need to refer to it by its alias.

 oc_product_to_category.product_id

should be

 opc.product_id

I recommend you remove all of the backticks and just write standard SQL. I see nothing in the query that justifies cluttering it with non-standard escape sequences; that includes TEXT as MySQL allows you to use it unescaped, along with a short list of other reserved words like ACTION, BIT, DATE, ENUM, NO, TIME and TIMESTAMP.

http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

In general, I avoid escaping identifiers, and I specifically avoid backticks because they are non-standard and non-portable. MySQL can handle the standard SQL quoting syntax of double quotes, if properly configured. On new databases I enable ANSI_QUOTES.

SET global sql_mode='ANSI_QUOTES';

Though you should never do this on a production database, because it will change the behavior of existing queries. Once enabled, you'll be able to use ANSI (standard) quoting, but the side effect is you can no longer use double quotes for string literal values, however, that is also a non-standard practice which only works on MySQL and should be avoided.

@Kaii Actually, no. MySQL doesnt requre you to escape TEXT as a column name. You can check the docs. It is explicitly allowed. Besides that, MySQL will still try to parse the statement the correct way in the presence of other reserved words. — codenheim, Jul 15 '14 at 15:39

Sadikhasan · Answer 2 · 2014-07-14T09:00:34.317

0

Change

SELECT `text`,
       `oc_product_to_category.product_id`

To

SELECT `text`,
       `oc_product_to_category`.`product_id`

OR Simply remove back ticks on field/table name if it is not reserve word. Here text is reserve word of mysql so it is written in backticks like

SELECT `text`,
       oc_product_to_category.product_id

You can put back ticks on field name, table name. You are just putting here as combine of tablename and field name above that's why you getting error.

edited Jul 14 '14 at 09:00

answered Jul 14 '14 at 08:48

Sadikhasan

18,365
21
80
122

3

Or even better: remove those dreaded (and not needed) backticks alltogether – Jul 14 '14 at 08:51

score 0 · Answer 3 · answered Jul 14 '14 at 10:26

oc_product_to_category.product_id

If you want to use backticks, then use it for your table and column separately, like this:

oc_product_to_category.product_id

Also, you have aliased your table, so use your alias, like this:

opc.product_id

But you do not need backticks in this case.

backtick quote causing error in sql query

3 Answers3