Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Unanswered: Query returning more rows than it should.

    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'

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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

  4. #4
    Join Date
    Mar 2004
    Posts
    480
    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.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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.

  7. #7
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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..

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat's explanation should have been clear too...

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

    except you were probably thinking of bondage...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •