my AND isn't working again, it isn't only showing what I want it to equal-CodePudding

So I had similar questions and it was answered, but this time I have the parenthesis in the right places and my last AND function isn't properly working, the property_type is showing other results than just "sidewalk". enter image description here

SELECT 
footprint_length,number_of_docks , property_type, station_id
FROM 
bigquery-public-data.austin_bikeshare.bikeshare_stations 
WHERE 
footprint_length  > 45
 AND (number_of_docks BETWEEN 1 AND 17) OR (number_of_docks = 19)
 AND property_type  = "sidewalk"

CodePudding user response：

If your goal is to show cases where the number of docks is between 1 and 17 or 19, I would delete the closed parenthesis after 17 and the open one before "number".

AND (number_of_docks BETWEEN 1 AND 17 OR number_of_docks = 19)

Currently, if number_of_docks=19 evaluates to true, the whole condition will probably show true.

Perhaps more simply, you could avoid the OR and parenthesis altogether and just change your range to 1-19 and then weed out 18 since that is effectively what you're doing anyway with the OR. AND is usually a little easier to manage in my opinion.

AND number_of_docks BETWEEN 1 AND 19 AND number_of_docks != 18 AND property_type='sidewalk'

CodePudding user response：

Your current criteria is equivalent to:

  (
    footprint_length  > 45 AND 
    number_of_docks BETWEEN 1 AND 17 AND 
    property_type  = "sidewalk" 
  )
OR 
  number_of_docks = 19

In other words OR number_of_docks=19 applies even if footprint_length <=45!

Did you want:

    footprint_length  > 45 AND 
    property_type  = "sidewalk" AND
    (
        (number_of_docks BETWEEN 1 AND 17)
        OR 
        number_of_docks = 19
    )