INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL 'AND' Operator

SQL 'AND' Operator

(OP)
I'm currently using the AND operator in a query. see below

WHERE TYPE <> '1' AND TYPE <> '2'

I need to add a 3rd and 4th AND operator. I believe the AND operator only works if the first condition AND the second condition are true

this is what I'm looking for

WHERE TYPE <> '1A' AND TYPE <> '2Z' AND TYPE <> 'RR' AND TYPE <> 'AD'

I'm basically trying to exclude those Types in my returned results.

any help would be appreciated

RE: SQL 'AND' Operator

The AND operator just combines two boolean values to one.

But in the same manner as you can write 1+1+1+2, though the + operator only works on two operands, you also can have three or four or whatever number of AND operations.

The order of processing them simply is from left to right, unless other operators have precedence or you use brackets.

In short: Your last where clause would exclude all 4 types. If that's what you want, it works. Simply try.

This is what is done, I emphasize the boolean values by parentheses:

CODE

(TYPE <> '1A') AND (TYPE <> '2Z') AND (TYPE <> 'RR') AND (TYPE <> 'AD')
1. compare for unequality of TEST and '1A' - If unequal: TRUE, else FALSE
2. compare for unequality of TEST and '2Z' - If unequal: TRUE, else FALSE
3. AND of results 1 and 2 - TRUE AND TRUE: TRUE, all other combinations: FALSE
4. compare for unequality of TEST and 'RR'. If unequal: TRUE, else FALSE
5. AND of results 3 and 4 - TRUE AND TRUE: TRUE, all other combinations: FALSE
6. compare for unequality of TEST and 'AD'. If unequal: TRUE, else FALSE
7. AND of results 5 and 6 - TRUE AND TRUE: TRUE, all other combinations: FALSE 

This is only TRUE, if all conditions are true. Only TYPE neither '1A' nor '2Z' nor 'RR' nor 'AD' are resulting in the overall TRUE.

Do you have further where clauses? Then you might want to use parenthesis to combine them correctly. One further operation can "destroy" your logic.
Virtually give this whole condition a name TYPECONDITIONS, then the further WHERE clause would perhaps look like this:

(TYPECONDITIONS) AND otherconditions - That would still mean only TYPES not in that range will get into the result
(TYPECONDITIONS) OR otherconditions - That would mean other conditions met will make the type unimportant, it could be anything, as long as otherconditions are met.
(TYPECONDITIONS) AND (othercondition1 OR othercondition2) - That nesting of OR means the TYPECONDITIONS still need to be fullfilled and besides that only one of the other conditions is enough.

The overall clause is what matters.

Bye, Olaf.

RE: SQL 'AND' Operator

"trying to exclude those Types in my returned results"

WHERE TYPE NOT IN ('1A', '2Z', 'RR', 'AD')

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: SQL 'AND' Operator

(OP)
perfect, that's exactly what I was looking for..

Thanks

RE: SQL 'AND' Operator

So you were just loking for a shorter way to write this? Why didn't you say so? Your question was about the AND operator.

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close