asked on

SQL Query w/multiple 'WHERE' statements

I need to run a query against a SQL Server 2005 database. I'm need to select all of the columns from one specific table where entries in the date column fall between 1/31/2013 - 12/31/2013; where entries in the company column = APP; and where entries in the 'basis' column = one or two or three or four.

This is what I have so far:

SELECT *
FROM Table1
WHERE DATE_COLUMN BETWEEN '2013-01-31' AND '2013-12-31'
AND COMPANY_COLUMN = 'APP'
AND BASIS_COLUMN = 'one' OR BASIS_COLUMN = 'two' OR BASIS_COLUMN = 'three' OR BASIS_COLUMN = 'four'

Unfortunately, its not working. What am I missing?

ASKER CERTIFIED SOLUTION

Dan Craciun

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve Wales

You are mixing AND and OR without parentheses. Never a good idea. The optimizer evaluates the AND's first and then the OR's (the standard evaluation order is NOT / AND / OR).

You may also (not 100% certain on this without testing) want to put the between part of the where clause in parentheses.

Try:

SELECT *
FROM Table1
WHERE (DATE_COLUMN BETWEEN '2013-01-31' AND '2013-12-31')
AND COMPANY_COLUMN = 'APP'
AND (BASIS_COLUMN = 'one' OR BASIS_COLUMN = 'two' OR BASIS_COLUMN = 'three' OR BASIS_COLUMN = 'four')

Open in new window

SELECT *
FROM Table1
WHERE (DATE_COLUMN BETWEEN '2013-01-31' AND '2013-12-31')
AND COMPANY_COLUMN = 'APP'
AND BASIS_COLUMN in ('one', 'two', 'three', 'four')

Open in new window

PortletPaul

Is between actually providing the results you expect? I might be wrong!
I always recommend NOT using between for date ranges.

It isn't absolutely necessary to bracket the between, but it's ok to do so. If you use a suggested alternative to between then it is definitely a good idea to bracket it together.

Additionally the safest way to specify a date in SQL Server is YYYYMMDD

I would re-write the query as:

SELECT *
FROM Table1
WHERE (DATE_COLUMN >= '20130131' AND DATE_COLUMN < '20140101')
AND COMPANY_COLUMN = 'APP'
AND BASIS_COLUMN IN ('one', 'two', 'three', 'four')

Why do I not use between for date ranges? see: "Beware of Between"