mysql not in returning blank rows

ok a bit of a weird one I’ve hit twice in the last week. First time I solved it by changing the query around (so I thought, but it’s more likely I updated the data).

This is a query that’s being run:-

select `option_id`, '' as `image`, '99' as `sort_order`, `Subproduct Code` from CSV, oc_option where `Product Code` = `main_jan` and `Subproduct Code` not in (select `jan` from oc_option_value) group by `Product Code`

Basically it has to insert values into a table where they don’t already exist, pulling values from 2 other tables.

The problem I was encountering is that data wasn’t being insert, the table only had 2 records so it should have been inserting over 500 additional rows.
Running:-

(select `jan` from oc_option_value)

returned the 2 rows, and running:-

select `option_id`, '' as `image`, '99' as `sort_order`, `Subproduct Code` from CSV, oc_option where `Product Code` = `main_jan`

returned over 500, so why wont it work together.

Well I finally stumbled upon someone making a passing remark about a not lookup selecting nulls. Looking at my results the 2 entries that were already there were in fact nulls, so I added

where `jan` is not null making the whole statement:-

select `option_id`, '' as `image`, '99' as `sort_order`, `Subproduct Code` from CSV, oc_option where `Product Code` = `main_jan` and `Subproduct Code` not in (select `jan` from oc_option_value where `jan` is not null) group by `Product Code`

And it’s now all working.

Leave a Reply

Your email address will not be published. Required fields are marked *