SQL query syntax error where the syntax appears to be correct

Question

I dont know what is the problem with this query. It seems very fine to me.

select o.id, p.date, u.billingName, u.billingPhone, u.billingCompany,
       u.billingAddress, u.billingCity, u.billingState, u.billingCountry,
       u.billingZip, u.shippingName, u.shippingPhone, u.shippingCompany,
       u.shippingAddress, u.shippingCity, u.shippingState, u.shippingCountry,
       u.shippingZip, o.layer, o.boardSizeWidth,o.boardSizeHeight, o.quantity
from order o, purchase p, useraccount u
where p.id = 1 and o.id = p.OrderId and u.id = p.UserId

Error : #1064 - 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 'order o, purchase p, useraccount u where p.id = 1 and o.id = p.OrderId and u.id ' at line 1

score 4 · Answer 1 · answered Apr 08 '13 at 15:10

4

The table order is a reserved word an needs to be escaped using backticks.

Also you should use standard ANSI JOIN syntax when joining your tables. See below for how this will be done.

select ....
from `order` o
inner join purchase p
    on o.id = p.OrderId
inner join useraccount u 
    on u.id = p.UserId
where p.id = 1

As a side note, you should avoid using reserved words for table and column names.

answered Apr 08 '13 at 15:10

Taryn

242,637
56
362
405

+1 over the other answer for actually taking the time to use the OP query and show the ANSI join syntax. – swasheck Apr 08 '13 at 15:31

John Woo · Accepted Answer · 2013-04-08T15:13:10.783

0

ORDER is a reserved keyword. It must be escape using backtick.

....FROM `Order` o

MySQL Reserved Keywords List

If you have a chance to alter the identifier, change the table name which is not present on the reserved keyword list. This will avoid you from future headache.

edited Apr 08 '13 at 15:13

answered Apr 08 '13 at 15:08

John Woo

258,903
69
498
492

two downvotes? what's wrong with this? It seems I'm getting downvotes every day for no reason. – John Woo Apr 08 '13 at 15:32
2

Wasn't me; but why do you care if you have 84.2k? – Kermit Apr 08 '13 at 15:34
Wasn't me, but I received one too. – Taryn Apr 08 '13 at 15:36
@FreshPrinceOfSO I know. I didn't tell it was you. Do you know what downvotes really means? asides from deducting two rep points? – John Woo Apr 08 '13 at 15:36
2

my impression is that it means that there's another answer available that's superior to the one being downvoted thus creating a clear differentiation between the two – swasheck Apr 08 '13 at 15:37
@JW I know what the Stack definition is. But you forget there are humans with these input devices called mice and they don't care. – Kermit Apr 08 '13 at 15:38
@swasheck I guess you right. bluefeets's answer is very good because it shows another point for using ANSI join, but is it really a reason to have a downvote for not showing it? I guess the main point of the question is what causes the error in the query right? – John Woo Apr 08 '13 at 15:43

SQL query syntax error where the syntax appears to be correct

2 Answers2