CODEWITHSUNDEEP
mysql
Michael Grenzer
Michael Grenzer

Reputation: 501

MySQL unknown column (is in table)

My table structure:

inventory_items:

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

My SQL-Query:

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

And i get the following Error:

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

The column is existing.... :(

Upvotes: 1

Views: 792

Answers (1)

xQbert
xQbert

Reputation: 35323

Short answer: your mixing join types. use Inner join or , notation, not both. Reason: the compiler attempts to complete the inner join statements first before generating the possibly large Cartesian on the , type join. So at the time the join is attempted, it has no knowledge of the value in question. If you truly mean cross join state it and problem should go away.

More on this can be found: 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

Upvotes: 3

Related Questions