Click here to Skip to main content
15,065,044 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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 ON customer.CustomerID=order.CustomerID LIMIT 0, 30' at line 2

What I have tried:

SELECT Customer.name, Customer.sex, Customer.address, order.orderDATE, Customer.nationality
FROM Customer INNER JOIN order ON customer.CustomerID=order.CustomerID
Posted
Updated 6-Jan-21 4:53am

Try:
SQL
SELECT c.name
     , c.sex
     , c.address
     , o.orderDATE
     , c.nationality
FROM Customer c
INNER JOIN Order o
        ON c.CustomerID = o.CustomerID;
But I'd change a few things.
1) Make your table name plural: Customers not Customer; Orders not Order: this reflects that the tables contain multiple items rather than a single Customer or a single Order.
2) Change CustomerID in your Customers table to just ID, and OrderID to just ID in your Orders table. You can't mix 'em up because you have to specify the table they come from Via FROM or JOIN, so why duplicate information via the column name?
Your JOIN then becomes:
SQL
ON c.ID = o.CustomerID;
   
v4
Comments
CHill60 6-Jan-21 11:22am
   
Doesn't a fairy die every time a table name is made plural? :laugh: (stolen from Essential Database Naming Conventions (and Style) - Justinsomnia[^])
OriginalGriff 6-Jan-21 11:58am
   
I sincerely hope so!
Never did like those flying pests ... :D
ORDER is a reserved word in MySQL. To use it as a table name, you need to quote the name everywhere you reference it.
SQL
SELECT Customer.name, Customer.sex, Customer.address, `order`.orderDATE, Customer.nationality
FROM Customer INNER JOIN `order` ON customer.CustomerID = `order`.CustomerID 
MySQL :: MySQL 8.0 Reference Manual :: 9.2 Schema Object Names[^]
MySQL :: MySQL 8.0 Reference Manual :: 9.3 Keywords and Reserved Words[^]

As Griff suggested, changing your table name to Orders would also resolve the problem.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900