Solved

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

Posted on 2004-09-09
6
1,655 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

617 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