Query returning more rows than it should.

Frunkie · #1 (**permalink**) 11-07-07, 10:16

Well, at least to me it is..

Why.. oh why am I getting 3 rows returned in my result set when I should only be getting 2?

the active column should be eliminating one row but instead includes it. If I delete either the city or zip constraint, it works fine by only giving me the active users.

I want to be able to search on 2 columns from one textbox. It is just cleaner that way. That is the reason for the "OR".

Can someone please tell me what I am doing wrong please?

Code:

WHERE
t1.active ='1' AND
t4.city =  'mesa' OR
t3.zip_Code =  '85210'

Pat Phelan · #2 (**permalink**) 11-07-07, 10:27

The AND binds more tightly than the OR does. What you need is:

Code:

WHERE t1.active ='1'
   AND (t4.city =  'mesa'
      OR t3.zip_Code =  '85210')

-PatP

Frunkie · #3 (**permalink**) 11-07-07, 10:39

Holy cow Pat, it worked.

Thanks.

I know that at one point I had it exactly in the same order as what you posted (minus the brackets) but it still returned 3 rows.

For fun, I removed the brackets and I again got 3 rows returned. Why do the brackets make it work correctly?

Thanks

Frank

guelphdad · #4 (**permalink**) 11-07-07, 11:43

without them the query reads as follows:

Code:

WHERE t1.active ='1'
   AND t4.city =  'mesa'
      OR t3.zip_Code =  '85210'

return any rows where active=1 and city ='mesa'
OR
give me any rows where zip_code='85210' irrespective of any other conditions.

r937 · #5 (**permalink**) 11-07-07, 11:45

Quote:

Originally Posted by fjm1967

Why do the brackets make it work correctly?

because ANDs take precedence over ORs

it's the same as multiplication and addition

2 + 3 * 4 = 24, not 20

if you want (2 + 3) * 4, you have to use parentheses

Frunkie · #6 (**permalink**) 11-07-07, 12:45

Quote:

Originally Posted by r937

because ANDs take precedence over ORs

it's the same as multiplication and addition

2 + 3 * 4 = 24, not 20

if you want (2 + 3) * 4, you have to use parentheses

Thanks Rudy.. I hate being such a nube.

Frunkie · #7 (**permalink**) 11-07-07, 12:46

Quote:

Originally Posted by guelphdad

without them the query reads as follows:

Code:

WHERE t1.active ='1'
   AND t4.city =  'mesa'
      OR t3.zip_Code =  '85210'

return any rows where active=1 and city ='mesa'
OR
give me any rows where zip_code='85210' irrespective of any other conditions.

Thank you also for the explanation.. It makes clear sense to me now..

r937 · #8 (**permalink**) 11-07-07, 12:49

pat's explanation should have been clear too...

"The AND binds more tightly than the OR does."

except you were probably thinking of bondage...

Frunkie · #9 (**permalink**) 11-07-07, 13:34

Quote:

Originally Posted by r937

pat's explanation should have been clear too...

"The AND binds more tightly than the OR does."

except you were probably thinking of bondage...

lol. You mean he wasn't talking about bondage?

Internet Services

Web Development

Digital Marketing

Consumer Tech