I’m just writing a query at the moment that filters out test records from a database. These records have a foreign-keyed value that identifies test apps, e.g. a table with:
id name 1 TEST 2 ANOTHER_VALUE 3 SOMETHING_ELSE
This is joined onto the main data table, sensibly with a query like this:
SELECT * FROM the_data LEFT JOIN the_values ON the_data.some_value = the_values.id WHERE the_values.name != 'TEST'
The gotcha is however, that this doesn’t work as you’d expect. If a record in “the_data” table doesn’t have a corresponding entry in “the_values” e.g. if “the_data.some_value” is 0 (as in my instance, it doesn’t need to have a corresponding value), then these entries are completely excluded. The solution (simple once you understand how it works…) is to check for NULL values too:
SELECT * FROM the_data LEFT JOIN the_values ON the_data.some_value = the_values.id WHERE (the_values.name IS NULL OR the_values.name != 'TEST')
January 27th, 2010 at 12:41 pm
yeah, left join returns null values. If you didn’t want the rows in the_data that didn’t have corresponding rows in the_values, I’d go for:
inner join the_values on (the_data.some_value = the_values.id and the_values.name != ‘TEST’)
January 27th, 2010 at 1:14 pm
Yeah – that’s my point, I *do* want the rows in the_data without rows in the_values – it’s only if the row value is explicitly ‘TEST’ that I want to exclude them.
My issue with it is that surely if I say “the_value is anything except TEST” surely a value being NULL != ‘TEST’? Bit illogical if you ask me…