多表连接MySQL多个外键

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

我在MySQL中加入时遇到了困难。我有4张桌子,都连在一起。

**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.

我收到错误1054:'on clause'中的未知列...或其他'别名'错误。

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

我认为从多个表中选择有一些问题......任何人都可以帮助我吗?每个回复都表示赞赏。谢谢

1 个解决方案

#1

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

#1