November 12, 2008 at 10:43 am
Hi Everyone,
In mytable, I have data like this:
myLetter myNumber
A 1
A 2
B 1
B 2
C 1
C 2
I want to select all the rows except where the myLetter is A AND the myNumber is 1. So my resultset should include A2 and B1.
If I try SELECT myLetter, myNumber FROM mytable where (myLetter <> 'A' and myNumber <> 1) it splits up the two conditions and excludes all rows that have either an A or a 1. Am I doing something wrong with my brackets? Or is there a different way of structuring this query? Thanks,
Tom
November 12, 2008 at 10:50 am
You're not applying the distributive logic rule correctly. the rule is:
NOT (X AND Y) = (NOT X) OR (NOT Y)
So your criteria is supposed to be:
WHERE
(myLetter <> 'A' OR myNumber <> 1)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 12, 2008 at 10:57 am
Simple boolean error. Write the query to first return what you want to exclude.
After that, NOT the condition. If you expand that, you'll find that NOT(myLetter = 'A' and myNumber = 1) is actually
myLetter <> 'A' or myNumber <> 1.
Here is my test code to show you what I mean:
create table #TestTab (
myLetter char(1),
myNumber int
);
insert into #TestTab
select 'A', 1 union all
select 'A', 2 union all
select 'B', 1 union all
select 'B', 2 union all
select 'C', 1 union all
select 'C', 2;
select
myLetter,
myNumber
from
#TestTab
where
(myLetter = 'A'
and myNumber = 1);
select
myLetter,
myNumber
from
#TestTab
where
not(myLetter = 'A'
and myNumber = 1);
select
myLetter,
myNumber
from
#TestTab
where
(myLetter <> 'A'
or myNumber <> 1);
drop table #TestTab;
Edit: I also see that Matt beat me to this.
November 12, 2008 at 11:08 am
Thanks to both of you on this. I can see that you're both right. And I guess the bottom line is that now I know the answer I can remember it for next time.
The solution where you put the NOT in front of the brackets seems very clear to me. However, to my feeble brain, using the OR command seems counter-intuitive. Guess I missed Distribution Logic class or something! 🙂
Anyway, thanks guys, you were both very helpful,
Tom
November 12, 2008 at 11:14 am
I had boolean logic hammered into me in a microprocessor design course/lab in college. Couldn't explain it well, but I sure do remember how it works.
November 13, 2008 at 7:29 am
OK it turns out the example I gave was overly simplistic (hmmm, maybe that applies to me too! :unsure: ). Does the same logic apply when using NOT IN ()?
Instead of using 'not equals' to a specific value, what I'm actually trying to do is select rows where two fields are not present in two fields of another table. Something like this:
SELECT
fldID1, fldID2, fldCode, fldLdesc
FROM
mySourceTable
WHERE
(mySourceTable.fldID1 NOT IN (SELECT fldID1 from myDestTable)
AND
mySourceTable.fldID2 NOT IN (SELECT fldID2 from myDestTable))
I have tried the above using both AND and OR, but neither seem to give me the result I want (which would be all rows from mySourceTable where the combination of both fldID1 and fldID2 are not present in myDestTable). Either way the query returns no rows, even though there are definitely rows in mySourceTable that have a combination of fldID1 and fldID2 not found in myDestTable. Am I missing something? Thanks,
Tom
November 13, 2008 at 7:37 am
Now you're changing the type of question. The two can't be looked at separately, since you're trying to make sure the combination exists. It's not whether each vailue exists individually, it's whether there's a row in your lookup table that matches the combo.
Look at using either a NOT EXISTS clause or a LEFT OUTER JOIN. Something like:
SELECT
fldID1, fldID2, fldCode, fldLdesc
FROM
mySourceTable
WHERE
NOT EXISTS
(
SELECT NULL
from myDestTable
WHERE mysourcetable.fldid1= mydesttable.fldid1 AND
mysourcetable.fldid2= mydesttable.fldid2
)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 13, 2008 at 7:55 am
Thanks Matt,
You're right, I didn't ask the correct question originally which is sometimes half the trick. Thanks for the clear explanation - your answer has shown me exactly how to do what I wanted,
Tom
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply