Problem with 'not in' SQL command on string field - known error/bug?

Posted on 2004-09-09
Last Modified: 2012-08-13

When I run the following query:

SELECT Sourcetable.question
FROM Sourcetable
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:

SELECT Sourcetable.question
FROM Sourcetable
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:

SELECT Sourcetable.question
FROM Sourcetable
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. :) )
Question by:BadKarma
  • 3
  • 3

Expert Comment

ID: 12015529
I would go with a Left Outer Join rather then the not in subquery syntax. However, whenever I have tried "not" it has worked fine and I have done plenty of string comparisons.

Left Outer Join Syntax -
Select SourceTable.Question from
SourceTable a Left Join AnswerTable b on
where b.question is null

As an aside, are you sure that there are no questions in the sourcetable that are not in the answer table?
Your original query doesn't necessarily prove it because there could be the same question twice in the source table with a different index.


Author Comment

ID: 12016291
Actually, I have also done string comparisons with "not" before without trouble - but this time it's not working. There are some fairly long strings there, though, and some have special characters such as ' or " - which still shouldn't be a problem, but something's still rotten in the state of Denmark.

I wish the info wasn't proprietary, or I'd put up the tables.

As to your answer - I think I see where you're going, but the matter is a bit more complex. Your solution works to a degree, but not in my particular instance, I think. Allow me to sketch:

I basically have a user that can define a Department as a collection of Subdepartments, like so:

SubDepartments in Department
Department_Id    Subdepartment_Name
1                             Subdep1
1                             Subdep2
2                             Subdep3

In other words, department_Id 1  has subdepartments Subdep1 and Subdep2
Department_Id 2 has one subdepartment of type Subdep3

The source table defines whether a question is valid for a type of subdepartment.

Index     Question    SubDepartment_Name
1              a?                Subdep1
2              b?                Subdep1
3              b?                Subdep2
4              d?                Subdep2
5              e?                Subdep3

So the question "b?" is valid for both Subdep_Name's Subdep1 and Subdep2. All the other questions are valid for just one Subdep each.

(Note: I am aware that in this example, the queries that I posted earlier will not give the same resulting recordsets. In that case, the question was simplified).

Now, the Answertable is basically a re-organised list of question for the appropriate departments. So for department_Id 1, which has subdepartments Subdep1 and Subdep2, the question "b?" should appear only once, which is handled by a Group command in the SQL. (I am aware distinct would also be a possibility, but in this case due to other concerns a GROUP is needed.)

This would be the 'proper' Answertable for the Departments:

Index     Question   Department_Id
1               a?                1
2               b?                1
4               d?                1
5               e?                2

The user will then be able to select his department, and get an interface to answer the appropriate questions for him.

Now, at some point the user may redefine the subdepartments in a department, and in that case, I want the Answertable to adapt to this new definition. I wish to keep the answers that have already been given, but I want to add the new questions that have now become appropriate for that Department to the Answertable.

For instance, say that Department_Id 1 received the additional SubDep_Name Subdep3, then the question "e?" would have to be added to the Answertable for Department_Id 1. If Department_Id 2 received Subdep1, "a?" and "b?" would have to be added to the Answertable for Department_Id 2.

To do so, I have an insert query, with the earlier asked question being a subquery - namely, how do I find the (newly appropriate) questions from Sourcetable that aren't yet present in Answertable - for a specific Department_Id. The "Not in" construction would seem to be the simple and elegant thing to use, and not a left join with the "WHERE b.question is null"

This wasn't really my original question, of course, so if you want to give this one a crack, I'll up the amount of points when I accept your answer. :)

To the aside: yes, there are questions in the source table that are not in the answer table - that's the whole point. In fact, you are correct that sometimes the same question can be present in the source table with a different index, but I can solve that problem through judicious use of GROUP BY and MAX statements - I think.

And I'll still add that primary key, of course.


Accepted Solution

szacks earned 100 total points
ID: 12019201
Have you tried adding a record to the source table with a question that is not real to ensure that the query is not working?
Other then that, I still prefer the left join syntax, but that's probably personal preference.
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


Author Comment

ID: 12024182
'not real' - you mean just adding a record to the source table? Yes, I have. It's still not working.

I can understand you'd prefer the left join syntax, but the 'where b.question=null' won't work in the table construction above, I thought? I'll try to get it to work again (heck, I'll have to. :) )

Author Comment

ID: 12025035
Ok, I did manage to get it to work, like this (from the WHERE statement of the larger query this is a part of):

(Tekstentabel.Vraag in (SELECT Tekstentabel.Vraag
FROM [select * from Antwoorden where afdeling_Id=1]. AS Hulp RIGHT JOIN Tekstentabel ON Hulp.Vraag = Tekstentabel.Vraag
WHERE (((Tekstentabel.Vraag) Is Not Null) AND ((Hulp.Vraag) Is Null))))

I upped the point count and give you an A - since I learned something and it was quite useful. Ok?

Expert Comment

ID: 12056767
thank you

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now