Solved

SQL Query w/multiple 'WHERE' statements

Posted on 2014-03-26
3
551 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

914 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now