Solved

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

Posted on 2004-09-09
6
1,647 Views
Last Modified: 2012-08-13
All,

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. :) )
0
Comment
Question by:BadKarma
  • 3
  • 3
6 Comments
 
LVL 4

Expert Comment

by:szacks
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
a.question=b.question
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.

0
 

Author Comment

by:BadKarma
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.

Sourcetable
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:

Answertable
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.


0
 
LVL 4

Accepted Solution

by:
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.
0
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

by:BadKarma
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. :) )
0
 

Author Comment

by:BadKarma
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?
0
 
LVL 4

Expert Comment

by:szacks
ID: 12056767
thank you
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

758 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

27 Experts available now in Live!

Get 1:1 Help Now