jonm jonm - 5 months ago 17
MySQL Question

SQL left Join Error on multiple JOIN

Here is my query

SELECT item.item, item.id as itemID,item.item_sku,O.serial_no,transaction.id, transaction.t_price, transaction.t_unit, transaction.total_amount, transaction.transaction_type, transaction.comment, transaction.created from transaction

LEFT JOIN item ON transaction.item_id = item.id
LEFT JOIN order O ON transaction.order_no=o.order_no
WHERE transaction_type='buy' ORDER BY transaction.created DESC


But its showing this error .. why ??

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 'order o ON transaction.order_no=o.order_no



WHERE transaction_type='buy' ORDER ' at line

Answer

ORDER is a reserved word, so you need to escape it. Check this Stackoverflow answer on how to use back-tick to escape the reserved words in your queries.

Please check this one:

SELECT item.item, item.id as itemID,item.item_sku,O.serial_no,transaction.id, transaction.t_price, transaction.t_unit, transaction.total_amount, transaction.transaction_type, transaction.comment, transaction.created 
FROM transaction

LEFT JOIN item ON transaction.item_id = item.id
LEFT JOIN `order` O  ON transaction.order_no = o.order_no
WHERE transaction_type='buy' 
ORDER BY transaction.created DESC