MySQL 未知列(在表中)

Question

我的表结构:

库存项目:

CREATE TABLE `inventory_items` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(50) DEFAULT NULL COMMENT 'Usually used as invoice number',
 `vendor_id` int(10) NOT NULL DEFAULT '1',
 `item_type_id` int(10) unsigned DEFAULT NULL,
 `item_model_id` int(10) unsigned DEFAULT NULL,
 `condition_id` int(10) unsigned DEFAULT NULL,
 `item_functionality_id` int(10) unsigned DEFAULT NULL,
 `color_id` int(10) unsigned DEFAULT NULL,
 `quantity` int(10) unsigned DEFAULT NULL,
 `original_qty` int(10) DEFAULT NULL,
 `note` text,
 `zone_id` int(10) unsigned DEFAULT NULL,
 `rack_id` int(10) unsigned DEFAULT NULL,
 `shelf_id` int(10) unsigned DEFAULT NULL,
 `bin_id` int(10) unsigned DEFAULT NULL,
 `status` char(1) DEFAULT NULL COMMENT '1:CheckedIn;2:Transferred',
 `reserve` tinyint(1) NOT NULL DEFAULT '0',
 `log` text,
 `user_id` int(10) unsigned DEFAULT NULL,
 `created` datetime DEFAULT NULL,
 `modified` datetime DEFAULT NULL,
 `item_manu_model_id` int(11) NOT NULL DEFAULT '0',
 `cdma_carrier_id` int(11) DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=45334 DEFAULT CHARSET=latin1

我的 SQL 查询:

SELECT inventory_items.id,
       conditions.`name`,
       item_models.`name`
FROM   inventory_items,
       sorting_reports
       INNER JOIN item_models
               ON inventory_items.item_model_id = item_models.id
       INNER JOIN conditions
               ON inventory_items.condition_id = conditions.id
WHERE  inventory_items.item_model_id = 1111
       AND inventory_items.condition_id = 10
       AND inventory_items.id = 20
       AND ( ( inventory_items.zone_id = 21 )
              OR ( inventory_items.rack_id = 175 ) )
       AND sorting_reports.id != 0

我得到以下错误:

[Err] 1054 - Unknown column 'inventory_items.item_model_id' in 'on clause'

该列已存在.... :(

Answer 1

简短回答:您的混合联接类型。使用 Inner join 或 , 符号，不能同时使用。原因:编译器尝试在 , 类型连接上生成可能较大的笛卡尔之前先完成内部连接语句。所以在尝试连接时，它不知道所讨论的值。如果您真正的意思是交叉连接，说明它，问题就会消失。

可以找到更多相关信息:What is the difference between using a cross join and putting a comma between the two tables?

SELECT inventory_items.id,
       conditions.`name`,
       item_models.`name`
FROM   inventory_items 
       CROSS JOIN sorting_reports
       INNER JOIN item_models
               ON inventory_items.item_model_id = item_models.id
       INNER JOIN conditions
               ON inventory_items.condition_id = conditions.id
WHERE  inventory_items.item_model_id = 1111
       AND inventory_items.condition_id = 10
       AND inventory_items.id = 20
       AND ( ( inventory_items.zone_id = 21 )
              OR ( inventory_items.rack_id = 175 ) )
       AND sorting_reports.id != 0