MySQL operators

[molemenacer]

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

[obsidian]

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]

[molemenacer]

Thanks very much, that solved everything.