Problem with 'not in' SQL command on string field - known error/bug?
Posted on 2004-09-09
When I run the following query:
WHERE (Sourcetable.index not In (select Answertable.index from Answertable))
(Question is a text column, index is an integer column).
I get the result I want - a list of questions that are in the sourcetable but not in the answertable.
However, when I run:
WHERE (Sourcetable.question not In (select Answertable.question from Answertable))
I get an empty recordset as result - while I should be getting the exact same answer.
If I test the query:
WHERE (Sourcetable.question In (select Answertable.question from Answertable)) // 'Not' has been removed
I do get a list of questions, where each question is both in the SourceTable and the Answertable.
Now, my conclusion is that for some reason, the engine is having difficulty executing the 'not in' command on a string field/column. Is this is a known bug / error? If yes, is there a patch or anything similar?
(Note that I can already solve my problem by adding a autonumeric primary column - which I will do anyway. If I hadn't been receiving those tables from an external source, it would've already been present. :) )