Can I use same field in a query with different criteria

[capronton]

Can I use same field in a query with different criteria? For example I want query routes less than 100 and routes between 200 and 499

select route, bus, ttp
from table
where route > 100
and route between 200 and 499

Thanks for any help with this.

 

[SQLBill]

First, you need to reverse the > sign...that is greater than (WHERE route is greater than 100).

Second, yes that will work. However, I would suggest putting it in parenthesis to separate the different where's. Otherwise it reads as WHERE route is less than 100 AND also between 200 and 499.

SELECT ...
FROM ....
WHERE route < 100
  AND (route between 200 and 499)

I hope you realize, the between means you will only get routes from 201 to 498.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[jjefferson]

You need to say

WHERE route < 100 OR (route BETWEEN 200 AND 499)

You need the OR instead of AND to get routes less than 100 (0-99). Also, I tested BETWEEN with INT values and it picked up 200 and 499, unlike what SQLBill has suggested. If BETWEEN was not inclusive of the start/end values, I need to go back and check a ton of queries.

 

[capronton]

Thanks for all your help!

 

[SaltyTheFrog]

SQLBill - I know from years at this site your expertise is greater than mine but isn't Between inclusive?

create table #Between(MyNumbers int)
insert into #Between(MyNumbers) values(199)
insert into #Between(MyNumbers) values(200)
insert into #Between(MyNumbers) values(201)
insert into #Between(MyNumbers) values(202)
insert into #Between(MyNumbers) values(203)
insert into #Between(MyNumbers) values(204)

select * from #Between where MyNumbers between 200 and 203

Results:

200
201
202
203

 

[SQLBill]

SaltyTheFrog...

Wouldn't be the first time I was wrong. Between is such a pain.....
Thanks for correcting me.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

JJefferson,

Thanks for correcting me on the AND. I was so focused on the need for the parenthesis, I didn't think about it needing to be OR.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[jjefferson]

-SQLBill: Forest for the trees. Been there.

 

[philhege]

I think we've all been there.

I'd like to know if anyone gets non-NULL results from the original criteria <:-O

-----------
With business clients like mine, you'd be better off herding cats.