Jump to content

Archived

This topic is now archived and is closed to further replies.

molemenacer

MySQL operators

Recommended Posts

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 recieved

Thanks

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.