MySql NOT IN failing to return empty set

Question

I am currently having a problem when trying to select where a job is listed in the tbl_jobs table and has not been assigned to a delivery item in the tbl_delivery_items table by using a NOT IN subquery.

The sub query should return supplier_job_job_id 1 (which it does when you run this as a seperate query), with the NOT IN excluding the job with an id of 1. Alas, it is not working and causing me a headache by returningthe job with a job_id of 1 when I was expecting an empty set. Here is the codeigniter code generating the query:

$this->db->join("tbl_jobs", "tbl_jobs.job_id = tbl_supplier_jobs.supplier_job_job_id");

$this->db->where_not_in("supplier_job_job_id", "SELECT delivery_item_job_id FROM tbl_delivery_items");

$result = $query->result_array();

echo $this->db->last_query();

return $result;

Here is the query it generates:

SELECT * FROM (`tbl_supplier_jobs`) JOIN `tbl_jobs` ON `tbl_jobs`.`job_id` = `tbl_supplier_jobs`.`supplier_job_job_id` WHERE `supplier_job_job_id` NOT IN ('SELECT delivery_item_job_id FROM tbl_delivery_items') AND `supplier_job_supplier_id` = '1' ORDER BY `tbl_jobs`.`job_number` DESC

And here is the data:

tbl_supplier_jobs

supplier_job_id | supplier_job_job_id | supplier_job_supplier_id

1                 1                     1

2                 2                     2

tbl_jobs

job_id | job_number | job_description | job_delivered

1        1024         aaaaa             0

2        2048         bbbbb             0

tbl_delivery_items

delivery_item_id | delivery_item_delivery_id | delivery_item_job_id | delivery_item_toa | delivery_item_pallet_quantity | delivery_item_box_quantity

1                  1                           1                      2014-08-18 16:23:04 2                               1

Any ideas?

Answer 1

The problem is that the subquery is rendered as a string. You can see this clearly in the generated query that you supplied.

This seems to be a limitation in the where_not_in method of CodeIgniter. A possible solution, change the code to call the where method and render a slightly larger part of the query yourself:

$this->db->where("supplier_job_job_id NOT IN (SELECT delivery_item_job_id FROM tbl_delivery_items)");