molemenacer Posted September 21, 2006 Share Posted September 21, 2006 I have a query that works all apart from the last line, i show the query first:[code]where jobs.statusid in (6) and ifnull(jobs.currworkerid,'') like '%' and jobs.dictatorid like '%' and jobs.custcode like '%' and jobs.deptcode like '%' and jobs.specialitycode like '%' and ifnull(jobs.prevworkerid,'') like '%' and jobs.jobid like '%' and jobs.supplierid like '%' and Approveddate between '2006-09-14' and '2006-09-21' or Approveddate Is Null [/code]The where clause all works apart from the approveddate. When this runs it finds uses the criteria apart from approveddate is null and that returns every record that has a null entry in the approveddate field. It ignores that status = 6.i need a way of having approveddate part of the whole where clause and it looks for status=6 with approveddate = null.Any help would be gratefully recievedThanks Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 21, 2006 Share Posted September 21, 2006 by default, mysql is basically interpreting your clause like this:[code]where (jobs.statusid in (6) and ifnull(jobs.currworkerid,'') like '%' and jobs.dictatorid like '%' and jobs.custcode like '%' and jobs.deptcode like '%' and jobs.specialitycode like '%' and ifnull(jobs.prevworkerid,'') like '%' and jobs.jobid like '%' and jobs.supplierid like '%' and Approveddate between '2006-09-14' and '2006-09-21') or (Approveddate Is Null)[/code]you need to place parenthesis around the areas you want grouped like so:[code]where jobs.statusid in (6) and ifnull(jobs.currworkerid,'') like '%' and jobs.dictatorid like '%' and jobs.custcode like '%' and jobs.deptcode like '%' and jobs.specialitycode like '%' and ifnull(jobs.prevworkerid,'') like '%' and jobs.jobid like '%' and jobs.supplierid like '%' and (Approveddate between '2006-09-14' and '2006-09-21' or Approveddate Is Null) [/code] Quote Link to comment Share on other sites More sharing options...
molemenacer Posted September 21, 2006 Author Share Posted September 21, 2006 Thanks very much, that solved everything. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.