• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

where clause being ignored

hi all

i have some sql with a where clause that is being ignored

if i do this the where clause is executed

SELECT
  field1, field2, field3, field4
FROM
  t1 t1 inner JOIN t2 t2 ON t1.id = t2.id
  LEFT OUTER JOIN t3 t3 on t1.id = t3.id
  LEFT OUTER JOIN t4 t4 ON t1.id = t4.id
WHERE
  field1 NOT LIKE '%@some.text%'


BUT if i put another clause in

eg

SELECT
  field1, field2, field3, field4
FROM
  t1 t1 inner JOIN t2 t2 ON t1.id = t2.id
  LEFT OUTER JOIN t3 t3 on t1.id = t3.id
  LEFT OUTER JOIN t4 t4 ON t1.id = t4.id
WHERE
  field1 NOT LIKE '%@some.text%'
  AND CAST(field3 AS DATE) IS NULL OR field3 <= DATE_SUB(NOW(), INTERVAL 7 DAY)
  AND t3.field4 <> 2 OR t3.field4 IS NULL

this field1 NOT LIKE '%@some.text%' gets ignored

can someone tell me why this is happening

many thanks
0
dalecon
Asked:
dalecon
  • 2
1 Solution
 
neonpillCommented:
You have an OR in your WHERE statement.  This means that any fields where

"field3 <= DATE_SUB(NOW(), INTERVAL 7 DAY)"

will be returned, and your other filters will be ignored for that field.  It might make sense to put some brackets around the clause

"CAST(field3 AS DATE) IS NULL OR field3 <= DATE_SUB(NOW(), INTERVAL 7 DAY)"

if that is what you want to accomplish.
0
 
daleconAuthor Commented:


many thanks
putting brackets around CAST(field3 AS DATE) IS NULL OR field3 <= DATE_SUB(NOW(), INTERVAL 7 DAY)" worked and gave me the results i need


i wouldn't have thought that because the field with the not like clause is a varchar field and i was trying to filter out certain dates, works now anyway

i was tempted to insert everything into a temp table and then do the where clause with the not like on that, which gave me what i wanted.  i knew i could do it in one statement just didn't think about brackets

thanks again



0
 
neonpillCommented:
No problem.  

Just keep in mind the boolean rules (AND reduces the selection, OR broadens it) and their scope.

Regards
Albie
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now