CODEWITHSUNDEEP
mysqlsql
Selimhan40
Selimhan40

Reputation: 13

SQL INNER JOIN multiple tables error

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

Upvotes: 1

Views: 75

Answers (2)

Funk Forty Niner
Funk Forty Niner

Reputation: 74217

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;

Upvotes: 1

user3821501
user3821501

Reputation: 61

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;

Upvotes: 0

Related Questions