SQL INNER JOIN multiple tables error

1
votes

SQL: 

SELECT * FROM `orderregel` 
INNER JOIN `klant` ON order.KlantId = klant.KlantId 
INNER JOIN `product` ON orderregel.ProductId = product.ProductId 
INNER JOIN `order` ON orderregel.OrderId = order.OrderId;

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., orderregel., product., klant. FROM orderregel INNER JOIN klant O' at line 1

My database: order(OrderId, KlantId(FK)), orderregel(OrderregelId, OrderId(FK), ProductId(FK), KlantId(FK)), klant(KlantId, Naam, Voornaam), product(ProductId, Naam)

I want to display product.Naam, klant.Naam from each orderregel who has an own order

2
mysqlsql

2 Answers

1
votes

This line:

INNER JOIN `klant` ON order.KlantId = klant.KlantId

you also need to tick the MySQL reserved word order

INNER JOIN `klant` ON `order`.KlantId = klant.KlantId

Notice where the error starts and tells you:

right syntax to use near 'order

Same thing for:

INNER JOIN `order` ON orderregel.OrderId = order.OrderId;

to 

INNER JOIN `order` ON orderregel.OrderId = `order`.OrderId;

Rewrite:

SELECT * FROM `orderregel` 
INNER JOIN `klant` ON `order`.KlantId = klant.KlantId 
INNER JOIN `product` ON orderregel.ProductId = product.ProductId 
INNER JOIN `order` ON orderregel.OrderId = `order`.OrderId;

Edit: as per OP's comment but with ticks around the MySQL order reserved word.

SELECT * FROM orderregel 
INNER JOIN klant ON orderregel.KlantId = klant.KlantId 
INNER JOIN product ON orderregel.ProductId = product.ProductId 
INNER JOIN `order` ON orderregel.OrderId = `order`.OrderId;
0
votes

Quick reordering of the inner join statements may help (not tested)

SELECT * FROM `orderregel` 
INNER JOIN `klant` ON orderregel.KlantId = klant.KlantId 
INNER JOIN `product` ON orderregel.ProductId = product.ProductId 
INNER JOIN `order` ON order.OrderId = order.OrderId;