CODEWITHSUNDEEP
mysqlsqlwhere-in
Eddie Martinez
Eddie Martinez

Reputation: 13910

SQL multiple where conditions not working

I have these 3 queries, that are pretty much the same except in the where statement:

Insert into t1(f1,f2,f3)
SELECT     t2.f1 AS f1, '' AS f2, t2.f2 AS f2, t3.f3 AS f3)                                                                                    
from  t2 
INNER JOIN t3 ON t2.f1 = t3.f1                       
WHERE     (t2.f4 = 'n') and t2.f5 < '2013-04-01' and t2.f6 in('sss','ttt','ddd')
GROUP BY t2.f4, t2.f5, t2.f6;



Insert into t1(f1,f2,f3)
SELECT     t2.f1 AS f1, '' AS f2, t2.f2 AS f2, t3.f3 AS f3)                                                                                    
from  t2 
INNER JOIN t3 ON t2.f1 = t3.f1                       
WHERE     (t2.f4 = 'n') and t2.f5 > '2013-03-31' and t2.f6 in('sss','ttt','ddd')
GROUP BY t2.f4, t2.f5, t2.f6;


Insert into t1(f1,f2,f3)
SELECT     t2.f1 AS f1, '' AS f2, t2.f2 AS f2, t3.f3 AS f3)                                                                                    
from  t2 
INNER JOIN t3 ON t2.f1 = t3.f1                       
WHERE     (t2.f4 = 'n')  and t2.f6 in('rrr','qqq','yyy')
GROUP BY t2.f4, t2.f5, t2.f6;

can't I combine them like the following query and they would be theoretically the same query? Because I tried exactly this and i get different outputs when I test results.

   Insert into t1(f1,f2,f3)
   SELECT     t2.f1 AS f1, '' AS f2, t2.f2 AS f2, t3.f3 AS f3)                                                                                    
   from  t2 
   INNER JOIN t3 ON t2.f1 = t3.f1    
   WHERE (t2.f4 = 'n') and (t2.f5 < '2013-04-01' or t2.f5 > '2013-03-31' and t2.f6 in         
   ('sss','ttt','ddd'))  or t2.f6 in ('rrr','ggg','yyy')
    GROUP BY t2.f4, t2.f5, t2.f6;

I also tried this, and it didn't match either(I pretty much just encompassed each of the previous where statments in parentheses and added an or in the combined statement):

 Insert into t1(f1,f2,f3)
   SELECT     t2.f1 AS f1, '' AS f2, t2.f2 AS f2, t3.f3 AS f3)                                                                                    
   from  t2 
   INNER JOIN t3 ON t2.f1 = t3.f1
   WHERE     ((t2.f4 = 'n') and t2.f5 < '2013-04-01' and t2.f6 in('sss','ttt','ddd')) or
((t2.f4 = 'n') and t2.f5 > '2013-03-31' and t2.f6 in('sss','ttt','ddd')) or ((t2.f4 = 'n')  and t2.f6 in('rrr','qqq','yyy'))

Upvotes: 0

Views: 3483

Answers (2)

pamphlet
pamphlet

Reputation: 2104

They are not the same query, since AND has precedence over OR.

So in your first attempt at a rewrite, you have:

(t2.f4 = 'n')
and  (t2.f5 < '2013-04-01' or t2.f5 > '2013-03-31' and t2.f6 in ('sss','ttt','ddd'))
or t2.f6 in ('rrr','ggg','yyy')

Anything that satisfies the third line satisfies the entire where clause.

In other words, you need parentheses around the first two lines:

((t2.f4 = 'n')
and  (t2.f5 < '2013-04-01' or t2.f5 > '2013-03-31' and t2.f6 in ('sss','ttt','ddd')))
or t2.f6 in ('rrr','ggg','yyy')

This might be helpful: SQL Logic Operator Precedence: And and Or

Upvotes: 4

PCG
PCG

Reputation: 1207

Does this not work?: 

Insert into t1(f1,f2,f3)
SELECT     t2.f1 AS f1, '' AS f2, t2.f2 AS f2, t3.f3 AS f3)                                                                                    
from  t2 
 INNER JOIN t3 ON t2.f1 = t3.f1                       
WHERE     ((t2.f4 = 'n') and t2.f5 > '2013-03-31' and t2.f6 in('sss','ttt','ddd'))
OR     ((t2.f4 = 'n') and t2.f5 < '2013-04-01' and t2.f6 in('sss','ttt','ddd'))
OR     ((t2.f4 = 'n')  and t2.f6 in('rrr','qqq','yyy'))
GROUP BY t2.f4, t2.f5, t2.f6;

Upvotes: 1

Related Questions