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.