CODEWITHSUNDEEP
c#mysqlmysql-error-1064
Felic Licia
Felic Licia

Reputation: 1

MySql Syntax Error (using joins)

I can't find the error in MYSQL Syntax:

"SELECT customer.CustomerCode, customer.CustomerName, customer.CustomerAddress, customer.CustomerHandphone, customer,CustomerEmail, product.ProductCode, product.ProductName, product.ProductPrice, orderdetail.OrderCode, orderdetail.ProductCode, orderdetail.ProductPrice, orderlist.OrderCode, orderlist.CustomerCode, orderlist.OrderPrice " +
                     "FROM customer INNER JOIN (product INNER JOIN (orderlist INNER JOIN(orderdetail INNER JOIN ON orderlist.OrderCode = orderdetail.OrderCode) ON product.ProductCode = orderdetail.ProductCode) ON customer.CustomerCode = orderlist.CustomerCode) " +
                     "Where orderlist.OrderCode [email protected] " +
                        "GROUP BY customer.CustomerCode, customer.CustomerName, customer.CustomerAddress, customer.CustomerHandphone, customer,CustomerEmail, product.ProductCode, product.ProductName, product.ProductPrice, orderdetail.OrderCode, orderdetail.ProductCode, orderdetail.ProductPrice, orderlist.OrderCode, orderlist.CustomerCode, orderlist.OrderPrice";"

Error is below 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ON orderlist.OrderCode = orderdetail.OrderCode) ON product.ProductCode = orderde' at line 1

Upvotes: -2

Views: 208

Answers (2)

Thiwain Medagama
Thiwain Medagama

Reputation: 21

This SQL syntax error is caused by improper placement of the ON clause. Below is better way to write your SQL query:

SELECT enter code here
    customer.CustomerCode, 
    customer.CustomerName, 
    customer.CustomerAddress, 
    customer.CustomerHandphone, 
    customer.CustomerEmail, 
    product.ProductCode, 
    product.ProductName, 
    product.ProductPrice, 
    orderdetail.OrderCode, 
    orderdetail.ProductCode, 
    orderdetail.ProductPrice, 
    orderlist.OrderCode, 
    orderlist.CustomerCode, 
    orderlist.OrderPrice
FROM 
    customer
    INNER JOIN orderlist ON customer.CustomerCode = orderlist.CustomerCode
    INNER JOIN orderdetail ON orderlist.OrderCode = orderdetail.OrderCode
    INNER JOIN product ON orderdetail.ProductCode = product.ProductCode
WHERE 
    orderlist.OrderCode = @orderdetail.OrderCode
GROUP BY 
    customer.CustomerCode, 
    customer.CustomerName, 
    customer.CustomerAddress, 
    customer.CustomerHandphone, 
    customer.CustomerEmail, 
    product.ProductCode, 
    product.ProductName, 
    product.ProductPrice, 
    orderdetail.OrderCode, 
    orderdetail.ProductCode, 
    orderdetail.ProductPrice, 
    orderlist.OrderCode, 
    orderlist.CustomerCode, 
    orderlist.OrderPrice;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Write the query with an ON clause following each JOIN. This is just so much easier to read and write and is probably your problem:

SELECT c.CustomerCode, c.CustomerName, c.CustomerAddress, c.CustomerHandphone, c.CustomerEmail,
       p.ProductCode, p.ProductName, p.ProductPrice,
       od.OrderCode, od.ProductCode, od.ProductPrice, od.OrderCode,
       ol.CustomerCode, ol.OrderPrice " +
FROM orderlist ol INNER JOIN
     orderdetail od
     ON ol.orderCode = od.OrderCode INNER JOIN
     customer c 
     ON c.CustomerCode = ol.CustomerCode INNER JOIN
     product p
     ON p.ProductCode = od.ProductCode;

In addition, the ORDER BY seems unnecessary. Typically it would be used with aggregation functions. If you are getting duplicates somehow (which seems unlikely) use SELECT DISTINCT.

Finally, I don't understand this:

Where ol.OrderCode = @orderdetail.OrderCode

I don't think @orderdetail.OrderCode is valid syntax. If you want to pass in a variable, it would look more like:

Where ol.OrderCode = @OrderCode

Upvotes: 1

Related Questions