Cannot spot the difference between these two queries

0
votes

Trying to clean up a long query I have come up with a modified version, but now the old query with certain parameters returns 6 rows, but my version returns none. I have been straining my eyes to spot the difference but cannot find any. Here are the WHERE clauses from the two versions with identical set of parameters:

The ugly (but working) version:

SELECT ... 
FROM table as a
WHERE 1=1 
and a.title LIKE '%Manager%' 
and a.status='Approved'  
and (a.effected_date<=now()) 
and ( 
    (DATE_ADD(a.effected_date, INTERVAL 30 DAY) >= now() AND a.is_hotjob=0) 
     or 
    (DATE_ADD(a.effected_date, INTERVAL 30 DAY) >= now() AND a.is_hotjob=1)
)

My cleaned (but broken) version:

SELECT ...
FROM `table`  AS `a`
WHERE CONCAT(`a`.`title`, ' | ', `a`.`job_detail_section`) LIKE '%Manager%' AND 
`a`.`status` = 'Approved' AND 
`a`.`effected_date` <= '2013-12-30' AND 
(`a`.`effected_date` >= '2013-11-30' OR `a`.`is_hotjob` = '1')
2
mysqlsql

2 Answers

1
votes

You got the last AND/OR backwards:

`a`.`effected_date` <= '2013-12-30' OR 
(`a`.`effected_date` >= '2013-11-30' AND `a`.`is_hotjob` = '1')

EDIT: after reformatting, the answer looks more like:

SELECT ...
FROM `table`  AS `a`
WHERE CONCAT(`a`.`title`, ' | ', `a`.`job_detail_section`) LIKE '%Manager%' AND 
`a`.`status` = 'Approved' AND 
`a`.`effected_date` <= now() AND 
`a`.`effected_date` >= DATE_ADD(a.effected_date, INTERVAL 30 DAY)

and POSSIBLY:

AND `a`.`is_hotjob` IN (0,1)
0
votes

As a substitute of-

(DATE_ADD(a.effected_date, INTERVAL 30 DAY) >= now() AND a.is_hotjob=0) 
 or 
(DATE_ADD(a.effected_date, INTERVAL 30 DAY) >= now() AND a.is_hotjob=1)

You could use the following-

(`a`.`effected_date` >= '2013-11-30' AND `a`.`is_hotjob` in (0, 1))

But according to the additional info i.e. a.is_hotjob has value either 0 or 1 then there is no need to mention it in the condition at all. So basically the following format will be sufficient-

`a`.`effected_date` >= '2013-11-30'