?
Solved

SQL Query w/multiple 'WHERE' statements

Posted on 2014-03-26
3
Medium Priority
?
567 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 2000 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

Application Discovery Service in AWS

In the era of the cloud, customers migrating away from their existing on-premise infrastructure. This requires lots of planning, strategies, and effort to identify their existing resources and determine how best to migrate.  Datacenter migrations happen in four phases -

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

801 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