Solved

SQL Query w/multiple 'WHERE' statements

Posted on 2014-03-26
3
559 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 35

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 49

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

724 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