I'm running the following query, which is being generated by CodeIgniter's DataMapper ORM as I try to query a deep relationship of the form order_product/order/order_status:
SELECT `order_products`.*
FROM (`order_products`)
LEFT OUTER JOIN `orders` orders ON `orders`.`id` = `order_products`.`order_id`
LEFT OUTER JOIN `order_statuses` order_order_statuses ON `order_order_statuses`.`id` = `orders`.`order_status_id`
WHERE `order_products`.`sku` IN ('SYB-SAMPLETEST8', 'Copy of SYB-SAMPLE#B')
AND `order_products`.`deleted` = 0
AND `orders`.`ext_created_on` >= '2014-05-05'
AND `order_products`.`deleted` = 0
AND `orders`.`ext_created_on` <= '2014-05-21'
AND `orders`.`deleted` = 0
AND `order_statuses`.`deleted` = 0
AND `order_order_statuses`.`sales_data` = 1
I'm receiving the following error:
ERROR 1054 (42S22): Unknown column 'order_statuses.deleted' in 'where clause'
My schema for order_statuses table is as follows:
+-------------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ext_id | int(11) | YES | | NULL | |
| ext_type_id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| sales_data | tinyint(1) | YES | | 0 | |
| deleted | tinyint(1) | NO | | 0 | |
| created_on | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------------+--------------+------+-----+-------------------+----------------+
Why am I receiving this error even though the deleted column exists?
Because You are aliasing it to order_order_statuses.