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?

Answer 1

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.