多表联接MySQL多个外键 [英] Multiple table join MySQL multiple foreign keys

问题描述

我在使用MySQL时遇到困难.我有4张桌子，所有桌子都连接在一起.

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

我的查询如下:

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

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

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 

这篇关于多表联接MySQL多个外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！