and's and or's in SQL queries 2

[andycape]

I have a sql query that gets generated froman asp page, depending on what a user selects.

There could be any amount of :
- UserID 's
- Vehicles_Trips.VID 's

and the trip_diff and Mil_diff will be "<" or ">" a certain amount.

When I create the list of UserID's or VID's I use "or", and the other two I use "and", but this dosn't seem to be working. Example code below, this code still selects records where trip_diff and Mil_diff are over 50, which it should not.
(I have tried using all and's, and all or's - dosnt work either)

can anyone see where my and's / or's are not working correctly Thanx.

Select * from Vehicles_Trips left join Vehicles on Vehicles.VID = Vehicles_Trips.VID where Vehicles_Trips.VID= 3 or Vehicles_Trips.VID= 5 or UserID = 964 and Mil_diff < 50 and trip_diff < 50 order by Make, Start_Mil

 

[AtomicWedgie]

This should work

Select *
from Vehicles_Trips left join Vehicles on Vehicles.VID = Vehicles_Trips.VID
where (Vehicles_Trips.VID = 3
or Vehicles_Trips.VID= 5)
and UserID = 964
and Mil_diff < 50
and trip_diff < 50
order by Make, Start_Mil

Or try it like this.

Select *
from Vehicles_Trips left join Vehicles on Vehicles.VID = Vehicles_Trips.VID
where Vehicles_Trips.VID in (3,5)
and UserID = 964
and Mil_diff < 50
and trip_diff < 50
order by Make, Start_Mil

 

[SQLSister]

I find that whenever I need to mix And and Or, that the order of operations can give me unexpected results. Therefore I always put in the () to make sure things process in the order I intended.

For instance, all the stements below could give you different results:

Select * from Vehicles_Trips left join Vehicles on Vehicles.VID = Vehicles_Trips.VID where (Vehicles_Trips.VID= 3 or Vehicles_Trips.VID= 5) or UserID = 964 and Mil_diff < 50 and trip_diff < 50 order by Make, Start_Mil

Select * from Vehicles_Trips left join Vehicles on Vehicles.VID = Vehicles_Trips.VID where (Vehicles_Trips.VID= 3 or Vehicles_Trips.VID= 5 or UserID = 964) and Mil_diff < 50 and trip_diff < 50 order by Make, Start_Mil

Select * from Vehicles_Trips left join Vehicles on Vehicles.VID = Vehicles_Trips.VID where Vehicles_Trips.VID= 3 or Vehicles_Trips.VID= 5 or (UserID = 964 and Mil_diff < 50 and trip_diff < 50) order by Make, Start_Mil

Experiment with () placement until you get the results you want.

Incidentally using select * is a bad practice. You should never return more fields than you need because it uses unneeded processing time and network resources. If you have a join, at the very least you are returning the join field more than once. Likely there are other fields you don't need either.