How to use "and" and "or" in a "Where" clause

Question

I have a query that is gathering information based on a set of conditions. Basically I want to know if a location has paid out more than $50 for the day OR the comment section has the word "filter" in it...

My query is:

SELECT        Store_Id, Paid_Out_Amount, Paid_Out_Comment, Paid_Out_Datetime, Update_UserName, Till_Number
FROM            Paid_Out_Tb
WHERE        (Store_Id = 1929) AND (Paid_Out_Datetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0)) AND (Paid_Out_Datetime < DATEADD(day, DATEDIFF(day, 0, 
                         GETDATE()), 0)) AND (Paid_Out_Amount > 50) OR
                         (Paid_Out_Comment LIKE N'%' + 'Filter' + '%')

The problem is It returns 460 results and should only return 2.

score 7 · Answer 1 · answered Jul 23 '12 at 17:51

7

You need to wrap your Paid_Out_Amoutn and Paid_Out_Comment criteria in a second set of parentheses:

SELECT Store_Id, Paid_Out_Amount, Paid_Out_Comment, Paid_Out_Datetime, 
    Update_UserName, Till_Number 
FROM Paid_Out_Tb 
WHERE (Store_Id = 1929) AND (Paid_Out_Datetime >= 
    DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0)) AND 
    (Paid_Out_Datetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND 
    (
        (Paid_Out_Amount > 50) OR (Paid_Out_Comment LIKE N'%' + 'Filter' + '%')
    )

answered Jul 23 '12 at 17:51

LittleBobbyTables - Au Revoir

32,008
25
109
114

As this answer implies using ANDs combined with ORs without parenthesis can be confusing. AND has a higher order of precedence than OR, so parenthesis are needed to show what part you want considered by the OR statement. See https://msdn.microsoft.com/en-us/library/ms190276.aspx – bstrong Sep 03 '15 at 14:43
Another related question is here: http://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or. – bstrong Sep 03 '15 at 14:49

score 5 · Answer 2 · answered Jul 23 '12 at 17:55

5

It really depends on what you are using the OR for. You have Paid_Out_Amount is greater than 50 or Paid_Out_Amount is like N%Filter%. Add some brackets to you clause

WHERE 
(
    (1 = 1)
    AND
    (2 = 2)
)
OR
( 3 = 3 )

answered Jul 23 '12 at 17:55

Simon Hughes

117
5

score 4 · Answer 3 · answered Jul 23 '12 at 17:51

It looks like you are missing one set of brackets:

SELECT        
    Store_Id
    , Paid_Out_Amount
    , Paid_Out_Comment
    , Paid_Out_Datetime
    , Update_UserName
    , Till_Number
FROM            
    Paid_Out_Tb
WHERE        
    Store_Id = 1929
    AND Paid_Out_Datetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0)
    AND Paid_Out_Datetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
    AND
    (
        Paid_Out_Amount > 50
        OR
        LOWER(Paid_Out_Comment) LIKE '%filter%'
    )

You also need to look at that LIKE command - updated the code to set the comments all to lowercase and search for the word filter.

Also check out the command BETWEEN for your Paid_Out_Datetime check

Don't use between for datetime. Between is inclusive so you have to do some tricky stuff to only get the day you are looking for. Have a look at [this blog post](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common). — Mikael Eriksson, Jul 23 '12 at 18:06

score 3 · Answer 4 · answered Jul 23 '12 at 17:50

AND takes precedence over OR. You need to group your conditions if you want to control the precedence. Try this:

SELECT        Store_Id, Paid_Out_Amount, Paid_Out_Comment, Paid_Out_Datetime, Update_UserName, Till_Number
FROM            Paid_Out_Tb
WHERE        (Store_Id = 1929) AND (Paid_Out_Datetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0)) AND (Paid_Out_Datetime < DATEADD(day, DATEDIFF(day, 0, 
                     GETDATE()), 0)) AND ( (Paid_Out_Amount > 50) OR
                     (Paid_Out_Comment LIKE N'%' + 'Filter' + '%') )

score 3 · Accepted Answer · answered Jul 23 '12 at 17:55

SELECT        Store_Id, Paid_Out_Amount, Paid_Out_Comment, Paid_Out_Datetime, Update_UserName, Till_Number
FROM            Paid_Out_Tb
WHERE        (Store_Id = 1929) AND (Paid_Out_Datetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0)) AND (Paid_Out_Datetime < DATEADD(day, DATEDIFF(day, 0, 
                         GETDATE()), 0)) AND (Paid_Out_Amount > 20) OR
                         (Store_Id = 1929) AND (Paid_Out_Datetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0)) AND (Paid_Out_Datetime < DATEADD(day, DATEDIFF(day, 0, 
                         GETDATE()), 0)) AND (Paid_Out_Comment LIKE N'%' + 'Filter' + '%')

I coupled a bunch of your suggestions, and came out with the above. This works. Thanks!

score 2 · Answer 6 · answered Jul 23 '12 at 17:50

Try with following query:

SELECT        Store_Id, Paid_Out_Amount, Paid_Out_Comment, Paid_Out_Datetime, Update_UserName, Till_Number
FROM            Paid_Out_Tb
WHERE       ((Store_Id = 1929) AND (Paid_Out_Datetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0)) AND (Paid_Out_Datetime < DATEADD(day, DATEDIFF(day, 0, 
                         GETDATE()), 0)) AND (Paid_Out_Amount > 50)) OR
                         (Paid_Out_Comment LIKE N'%' + 'Filter' + '%')

score 1 · Answer 7 · answered Jul 23 '12 at 17:50

you need to enclose the first part of the where clause (up to 'OR') in paranthesis.

(
 (Store_Id = 1929) AND (Paid_Out_Datetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0)) AND (Paid_Out_Datetime < DATEADD(day, DATEDIFF(day, 0, 
                         GETDATE()), 0)) AND (Paid_Out_Amount > 50)

) OR (Paid_Out_Comment LIKE N'%' + 'Filter' + '%')

How to use "and" and "or" in a "Where" clause

7 Answers7