Jump to content


Photo

MySQL operators


  • Please log in to reply
2 replies to this topic

#1 molemenacer

molemenacer
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 21 September 2006 - 02:36 PM

I have a query that works all apart from the last line, i show the query first:

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 

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

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 21 September 2006 - 02:43 PM

by default, mysql is basically interpreting your clause like this:
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)

you need to place parenthesis around the areas you want grouped like so:
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) 

You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 molemenacer

molemenacer
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 21 September 2006 - 02:45 PM

Thanks very much, that solved everything.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users