This query doesn't give me the expected results?

Question

I have been trying all night to make it work, but it doesn't. When I give the date ranges for adate as 2014-03-12 it even shows me the data of previous dates. What am I missing here?

SELECT tno, type, ccno, paidamt, aby, adate, atime 
FROM payment 
WHERE 
    adate<='2014-03-12' 
    AND adate<='2014-03-12' 
    AND aby='anuradha' 
    AND type='ADD' OR type='REM' 
    AND delstat='0'

What kind of field is `adate` set to? It should be a `DATE` or `DATETIME` field, and if the data in the field is not stored in the same format `YYYY-MM-DD`, you should wrap `adate` in the `DATE()` function: `WHERE DATE(adate) <= '2014-03-12'`. P.s. your first two `WHERE` conditions are identical. — scrowler, Mar 12 '14 at 00:41
What data you have and what are you expecting? Show sample data. — Ravinder Reddy, Mar 12 '14 at 00:47

exussum · Answer 1 · 2014-03-12T00:51:17.030

2

Brackets

SELECT tno, type, ccno, paidamt, aby, adate, atime 
FROM payment 
WHERE 
    adate<='2014-03-12' 
    AND adate<='2014-03-12' 
    AND aby='anuradha' 
    AND (type='ADD' OR type='REM' )
    AND delstat='0'

Also adate is specified twice. You should probably remove the second one

SELECT tno, type, ccno, paidamt, aby, adate, atime 
FROM payment 
WHERE 
    adate>='2014-03-12' 
    AND adate<='2014-03-12' 
    AND aby='anuradha' 
    AND type='ADD' OR type='REM' 
    AND delstat='0'

edited Mar 12 '14 at 00:51

answered Mar 12 '14 at 00:41

exussum

18,275
8
32
65

I assume one of the a dates is supposed to be greater than it equal instead of both less than ? – exussum Mar 12 '14 at 00:48

score 2 · Answer 2 · answered Mar 12 '14 at 00:41

I assume your problem is related with that OR. Enclose it in parenthesis:

SELECT tno, type, ccno, paidamt, aby, adate, atime 
FROM payment 
WHERE 
    adate<='2014-03-12' 
    AND adate<='2014-03-12' 
    AND aby='anuradha' 
    AND (type='ADD' OR type='REM' )
    AND delstat='0'

score 2 · Answer 3 · answered Mar 12 '14 at 01:11

I wonder if you have a typo in

  adate<='2014-03-12' 
  AND adate<='2014-03-12'

This date-range part of the query is asking, umm, twice, for all rows on or before 12-Mar-2014. That might not be what you want.

Do you want this?

  adate >=  '2014-03-12'
  AND adate < '2014-03-12' + INTERVAL 1 DAY

That particular pair of inequalities is the most reliable way to get rows occurring on a particular day.

Also you may want to replace

AND type='ADD' OR type='REM'

with

AND type IN ('ADD', 'REM')

because it will likely perform better and get you out of trying to guess the associativity of AND and OR (which I believe you guessed wrong).

score 1 · Answer 4 · answered Mar 12 '14 at 00:55

when I give the date ranges for adate as 2014-03-12 it even shows me the data of previous dates... What Am I missing here?

Input to adate was wrong. You should have used different value when said a range, but you are not doing that and you are comparing for a date before of that. And hence previous date results.

Try this way:

set @startDate = '2014-03-01';  -- as an example
set @endDate = '2014-03-12';  

SELECT tno, type, ccno, paidamt, aby, adate, atime 
FROM payment 
WHERE 
    adate between @startDate and @endDate
    AND aby='anuradha' 
    AND type in ('ADD', 'REM') 
    AND delstat='0'

score 0 · Answer 5 · edited May 23 '17 at 10:25

adate<='2014-03-12' AND adate<='2014-03-12'

seems like a pretty useless duplication of a condition here.

Next, as your statement is formulated now, due to precedence of logical operators, it's evaluated as

( adate<='2014-03-12' AND adate<='2014-03-12' AND aby='anuradha' AND type='ADD')
OR 
(type='REM' AND delstat='0')

which is probably not what you want, and explains why earlier dates seep through (through the right side of the OR condition.

A rather complete overview of operator precedence in SQL can be found here: SQL 'AND' or 'OR' comes first? and here: SQL Logic Operator Precedence: And and Or

This query doesn't give me the expected results?

5 Answers5