mysql - using reserved names as column name

Question

I am trying to run this query but doesn't run

SELECT ng.parent_id, ng.tab, ng.post_id, ng.ORDER, ng.cluster_key, pd.id, pd.slug, pd.link_text, pd.parent
FROM `ecom_navigation` AS ng, `ecom_page_data` AS pd
WHERE ng.cluster_key = 'primary'
AND ng.tab = '0'
AND ng.parent_id = '1'pd.id = ng.post_id
ORDER BY ng.order

and getting error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pd.id = ng.post_id ORDER BY ng.order' at line 1

i've tried `ng.order` and `ng`.`order` but still cant seem to get it to run.

Any help is much appreciated

possible duplicate of [Syntax error due to using a reserved word as a table or column name in MySQL](http://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql) — rene, May 11 '14 at 12:36

score 2 · Accepted Answer · answered Oct 17 '11 at 04:07

2

I think you're just missing an and:

AND ng.parent_id = '1'pd.id = ng.post_id

should probably be:

AND ng.parent_id = '1'
AND pd.id = ng.post_id

Or better, put the join condition in the join (and don't quote your numbers unless they really are strings):

SELECT ng.parent_id, ng.tab, ng.post_id, ng.ORDER, ng.cluster_key, pd.id, pd.slug, pd.link_text, pd.parent
FROM `ecom_navigation` AS ng join `ecom_page_data` AS pd on ng.post_id = pd.id
WHERE ng.cluster_key = 'primary'
AND ng.tab = 0        -- Leave the quotes on if tab is a string
AND ng.parent_id = 1  -- Leave the quotes on if parent_id is a string
ORDER BY ng.order

answered Oct 17 '11 at 04:07

mu is too short

426,620
70
833
800

as if I did not see that!! Cheers – Phil Jackson Oct 17 '11 at 04:09
@Phil: Come on, how many times has it been something that couldn't possibly be wrong? More than once no doubt :) – mu is too short Oct 17 '11 at 04:13

score 1 · Answer 2 · answered Oct 17 '11 at 04:07

1

try

ng.order

if i could get backticks to show in this editor, then they would be there, but it seems to remove them

backticks are used to escape reserved words and spaces in column names etc

answered Oct 17 '11 at 04:07

bumperbox

10,166
6
43
66

score 0 · Answer 3 · answered Oct 17 '11 at 04:08

Change your query to (you have a ' misplaced)

SELECT ng.parent_id, ng.tab, ng.post_id, ng.ORDER, ng.cluster_key, pd.id, pd.slug, pd.link_text, pd.parent
FROM `ecom_navigation` AS ng, `ecom_page_data` AS pd
WHERE ng.cluster_key = 'primary'
AND ng.tab = '0'
AND ng.parent_id =  '1' AND pd.id = ng.post_id
ORDER BY ng.order

OR

SELECT ng.parent_id, ng.tab, ng.post_id, ng.ORDER, ng.cluster_key, pd.id, pd.slug, pd.link_text, pd.parent
FROM `ecom_navigation` AS ng, `ecom_page_data` AS pd
WHERE ng.cluster_key = 'primary'
AND ng.tab = '0'
AND ng.parent_id =  1 AND pd.id = ng.post_id
ORDER BY ng.order

mysql - using reserved names as column name

3 Answers3