MS-SQL: Why use an "AND" operator instead of an "OR" Operator here?

[KAMAZON]

Looking through chapter 3 of "SQL For Beginners" I ran across this simple query.
The query asks to get the name and zip code of all members who do not live on a street which ends with "Street" or "Road", such as "Cherry Road" or "Soto Street". Here is my simple query:

SELECT FirstName + ' ' + LastName AS [Member Name], Zip
FROM MemberDetails
WHERE Street NOT LIKE '% Road' OR Street NOT LIKE '% Street'

The book insists on using AND, and even goes as far as saying "AND" is the obvious choice, but dosen't explain why. I thought the "AND" operator forced it to fit both restrictions. An "OR" operator seems like the only one that would work.

It also has a space after the wildcard (%). Is that space really necissary? Dosen't the wildcard also include spaces as a character anyways? Couldn't I just do do a &Road which would also look for the space and return the exact same result? I would try this out on my home SQL test server but I'm far away from it and my RDP isn't working, so I figured i'd pick your brains for a minute and stop picking my nose. Thanks in advance!

 

[Aikouka]

Because that's the logical negation:

Statement: "Contains Street or Road"
Logicalized: Vx (Sx ^ Rx)

Statement: "Does Not contain Street or Road"
Logicalized: Vx`(Sx ^ Rx) = Vx(`Sx & `Rx)

EDIT: Fixed them to be a better logical statement. Sx being x contains "Street" and Rx being x contains "Road"

EDIT 2: To show the error in your statement, "Cherry Road" would be valid because "NOT LIKE '% Street'" would return 1 (or True) and then the logical statement would be "0 OR 1" (FALSE OR TRUE). Since one of the statements returned TRUE, the statement itself returns TRUE (1).

 

[imported_Rogie]

Your query would return would return everything in the table.
For example, you'd get people with "Cherry Road" because of this: OR Street NOT LIKE '% Street'
The book is correct in using AND.

Also, a space is necessary for someone that had this for the street: 127 Broad
If you don't use a space after the %, then the query would skip it, thinking it was valid.

 

[Rangoric]

Originally posted by: KAMAZON
I thought the "AND" operator forced it to fit both restrictions.

That is exactly why you are using it.

If it doesn't fit both restrictions then it COULD contain Road OR Street, just not both.

 

[DaveSimmons]

// In c/c++ with your OR:

int x ;

x = 3 ;

// pretend 3=street 4=road
if ( (x != 3) || (x != 4) ) {
printf("I are not 3!\n") ;
}

// Run this, you'll see the printf, because (x != 4) is TRUE.

 

[Aikouka]

To expound upon this a bit, your thinking is actually a common logical error that a lot of people do make, so I wouldn't worry about getting it wrong or anything. The reasoning why people tend to get it wrong is that they think, "It can't be this or this" and that sounds right in their head, because that statement translates (to us) as "if it's one of those, we don't want it".

 

[KAMAZON]

Thanks for the replies, I'm scratching my head a bit, and hope to understand this.

 

[reverend boltron]

<div class="FTQUOTE"><begin quote>Originally posted by: KAMAZON
Thanks for the replies, I'm scratching my head a bit, and hope to understand this.</end quote></div>

If you're having trouble understanding something in programming, my best advice to you is to draw it out. Get a pencil and paper and get to drawing. Do whatever you need to in order to get a better grip on what it is.

If you're doing this particular piece of code, draw it for each possible scenario with each operator.

So you would have your AND operator, and your OR operator. See which statement will get through the gate.

I drew it out for you.. graphic logic