October 8, 2003 at 9:53 am
I have a query with the following WHERE clause:
WHERE table.field1 NOT IN(21)AND
table.field2 NOT IN (44)
(numbers replaced with SELECT... statement)
The query returns a result that excludes records if either of the above is true. I was expecting the query to exclude records only if both of the above were true (like an OR instead of AND). Is this something to do with using NOT IN? I've done lots of sophisticated queries but this simple little one is a head scratcher for me. Any help would be appreciated.
Thanks
Tim
October 8, 2003 at 10:32 am
The NOTs are tying you up. Try using OR instead of AND. You need the row if either (or both) predicate is true, but not if they're both false, so that's an OR, not an AND. Isn't boolean math fun?
--Jonathan
--Jonathan
October 8, 2003 at 10:54 am
This has to do with boolean logic. I took a logic course in a long time ago and I can't remember all the details but basically when you are working with NOT in this way you have to reverse the AND to an OR because you have the NOT twice.
So replace your AND with an OR and you should get the result set that you expect.
Here's the symbolic logic (I think):
NOT(P AND Q) = NOT P OR NOT Q
October 8, 2003 at 12:54 pm
I need the row except when both NOTs are true. As it stands I don't get the row if either NOT is true.
Tim
October 8, 2003 at 1:51 pm
quote:
I need the row except when both NOTs are true. As it stands I don't get the row if either NOT is true.
Okay, but using NOT in the predicates flips the decision table. Pardon my ASCII art, but an AND is:
F T
-----
F | F F
T | F T
And an OR is
F T
-----
F | F T
T | T T
Can you see that putting the NOTs in flips the values and makes the OR what you need?
T F
-----
T | F T
F | T T
--Jonathan
--Jonathan
October 8, 2003 at 2:00 pm
Right you are. It makes perfect sense using a decision table as opposed to "talking" it through. And it works in the query. Thanks!
Tim
October 9, 2003 at 1:49 am
This is DeMorgan's law....
Not(A or B) = Not(A) and Not(B)
Not(A and B) = Not(A) or Not(B)
Not(Not(A)) = A
Where A & B are Statements
October 9, 2003 at 7:29 am
You may also want to change your NOT IN with NOT EXISTS and have your sub-select be SELECT 1 FROM tablen tn WHERE tn.field1 = field1).
October 10, 2003 at 4:59 pm
The problem is really with order of precedence. Forcing the order in which the clauses are evaluated should make the query work the way you think it should, e.g.:
WHERE (table.field1 NOT IN(21))
AND
(table.field2 NOT IN (44))
You don't need a parachute to skydive.
You only need a parachute to skydive twice.
You don't need a parachute to skydive.
You only need a parachute to skydive twice.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply