Solved

SQL Query w/multiple 'WHERE' statements

Posted on 2014-03-26
3
553 Views
Last Modified: 2014-04-15
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?
0
Comment
Question by:dowhatyoudo22
3 Comments
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 39957156
You forgot the parenthesis:
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

HTH,
Dan
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39957158
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


or

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

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39957714
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"
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question