SQL 'AND' Operator 1

[dvannoy]

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

 

[Olaf Doschke]

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:

(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:

[tt](TYPECONDITIONS) AND otherconditions[/tt] - That would still mean only TYPES not in that range will get into the result
[tt](TYPECONDITIONS) OR otherconditions[/tt] - That would mean other conditions met will make the type unimportant, it could be anything, as long as otherconditions are met.
[tt](TYPECONDITIONS) AND (othercondition1 OR othercondition2)[/tt] - 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.

 

[Andrzejek]

trying to exclude those Types in my returned results"
[tt]
WHERE TYPE NOT IN ('1A', '2Z', 'RR', 'AD')
[/tt]


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.

 

[dvannoy]

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

Thanks

 

[Olaf Doschke]

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.