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?

In the generated query, you can see the subquery is entirely in quotes, so it is treated as a string. `1` will not match the exact string `'select .. '`. — GolezTrol, Aug 19 '14 at 19:52

score 2 · Accepted Answer · answered Aug 19 '14 at 19:57

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)");

score 1 · Answer 2 · answered Aug 19 '14 at 19:53

The query isn't executing the subquery it is using the string value:

`supplier_job_job_id` NOT IN (
  'SELECT delivery_item_job_id FROM tbl_delivery_items'
)

Will check if supplier_job_job_id equals the string 'SELECT delivery_item_job_id FROM tbl_delivery_items'.

You should consider a LEFT JOIN to tbl_delivery_items and a WHERE condition of delivery_item_job_id IS NULL.. which should be fairly easy in your framework.

score 1 · Answer 3 · answered Aug 19 '14 at 19:53

Your subselect is being output as a string. Note that it is in single quotes in your resulting query. That of course will not work.

I would actually question your intended approach here. As your tbl_delivery_items table gets bigger and bigger your query will get slower and slower. This is not a scalable approach. You should revisit your table schema and get a more direct way of flagging completed deliveries.

MySql NOT IN failing to return empty set

3 Answers3