Conjunctive Operators 3

[LawnBoy]

I have a table of people's first and last names and employee #. I want to show everybody except Joe Blow. My TSQL is

SELECT LNAME, FNAME FROM TABLE
WHERE FNAME <> 'JOE'
AND LNAME <> 'BLOW'
ORDER BY LNAME;

My problem is that I'm not getting a logical AND, I'm getting a logical OR. It doesn't report Joe Blow, but it also doesn't report Jane Blow or Joe Smith.

I've tried using != instead of <>, same results.

Can someone spare half a brain cell and show me what I'm doing wrong?

TIA.

 

[SQLDenis]

what happens now?

SELECT LNAME, FNAME FROM TABLE
WHERE (FNAME <> 'JOE'
OR LNAME <> 'BLOW')
ORDER BY LNAME;

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[LawnBoy]

That works, but why? Is the conjuctive inverted by the <>, or what?

Thanks.

 

[PWise]

try

SELECT LNAME, FNAME FROM TABLE
Where 
FNAME + LNAME <>'JOE'+'BLOW'

 

[SQLDenis]

It is reversed. look
select * from table where lname ='blow' and fname ='joe'

what happens here? first all rows that have name ='blow' are retrieved out of that all rows that have fname ='joe' are returned

however

select * from table where lname <> 'blow' and fname <> 'joe'

what happens here?

first all rows that have don't have name ='blow' are retrieved out of that all rows that don't have fname ='joe' are returned

so it is a little trickier


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[LawnBoy]

Pwise - That works too, thanks, but doesn't really fit my need. I'm actually working on a much more complex view and had distilled my problem down to a basic principle I wasn't understanding.

SQLDenis - I think I follow... The conditionals are processed sequentially, with each additional condition based on the result set of the previous condition.

Which means the order of the conjuctive conditions is critical.

Thanks very much!

 

[gmmastros]

This is a very similar question, but with a modified way to filter the results.

thread183-1350916

Take a look at the example. Hopefully it will make sense. If not, let me know.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LawnBoy]

Yeah, I see. Thanks.