MySQL query gives wrong result

Question

I have to write a MySQL query for fetching result from my database with a specified date and code like ABH18 or ABH17 or ABH19. I write the query like below.

select * from tbl_transaction where
  trans_name like 'ABH19%' or trans_name like 'ABH18%' 
  or trans_name like 'ABH17%' or trans_name like 'ABH15%' 
  and date_of_vazhipad='2013-11-20' and trans_status='Completed successfully.'

There are no results in the DB satisfying all above conditions.But I got a result with different dates. But I want to ensure the date_of_vazhipad='2013-11-20' so that the query may work properly.

score 3 · Accepted Answer · answered Nov 02 '13 at 09:49

AND has a priority over OR. Put parentheses in the right places:

SELECT * FROM tbl_transaction WHERE
(trans_name like 'ABH19%' OR 
 trans_name like 'ABH18%' OR 
 trans_name like 'ABH17%' OR 
 trans_name like 'ABH15%') 
AND date_of_vazhipad='2013-11-20' 
AND trans_status='Completed successfully.'

score 1 · Answer 2 · edited May 23 '17 at 12:13

1

You can use REGEXP to shorten the query

Regexp is '^ABH19|^ABH18|^ABH17|^ABH15'

i.e.

SELECT * FROM tbl_transaction 
  WHERE trans_name REGEXP '^ABH19|^ABH18|^ABH17|^ABH15'
   AND date_of_vazhipad='2013-11-20' 
   AND trans_status='Completed successfully.';

Found reference here

edited May 23 '17 at 12:13

Community

1
1

answered Nov 02 '13 at 09:54

Naveen Kumar Alone

7,536
5
36
57

MySQL query gives wrong result

2 Answers2