mysql join 外键_多表连接MySQL多个外键

I am having difficult times with joins in MySQL. I have 4 tables, all connected together.

**Order**: order_id, shop_id(fk), date, day, order_price, PK_order

**Scan** : scan_id(PK), item_id, order_id(FK), stack, stack_price, price, note

**Item** : item_id(PK), item_name

**Shop** : shop_id(PK), shop_name

I want to build a query that outputs something like this:

date|day|shop_name|item_name|stack|stack_price|price|note

My query looks like this:

Select

order.date, order.day, shop.shop_name, item.item_name, scan.stack, scan.stack_price,

scan.price, scan.note

From

order, scan, shop, item

Join

shop on order.shop_id = shop.shop_id

Join

item on scan.item_id = item.item_id

I get error 1054: Unknown column ... in 'on clause', or the other 'alias' error.

However when I select only one column from one table, i get it working.

This query works:

select item.item_name from scan inner join item on scan.item_id = item.item_id

I think there is some problem with selecting from multiple tables... Anyone can help me? Every reply is appreciated. Thanks

解决方案

You have combined comma separated join and Inner join where you have used same table more than once which is not needed.

If am not wrong this is what you are looking for

SELECT `order`.`DATE`,

`order`.`day`,

shop.shop_name,

item.item_name,

scan.stack,

scan.stack_price,

scan.price,

scan.note

FROM `order`

join scan

ON `order`.order_id = scan.order_id

join shop

ON `order`.shop_id = shop.shop_id

join item

ON scan.item_id = item.item_id