CODEWITHSUNDEEP
mysql
user3408487
user3408487

Reputation: 1

This query doesn't give me the expected results?

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'

Upvotes: 0

Views: 70

Answers (5)

O. Jones
O. Jones

Reputation: 108686

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).

Upvotes: 2

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

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'

Upvotes: 1

exussum
exussum

Reputation: 18550

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'

Upvotes: 2

fvu
fvu

Reputation: 32953

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

Upvotes: 0

Barranka
Barranka

Reputation: 21047

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'

Upvotes: 2

Related Questions