mySQL Question - Mixing AND and OR

Discussion in 'OT Technology' started by Slid., May 14, 2004.

  1. Slid.

    Slid. I'm a guy.

    Joined:
    Oct 25, 2001
    Messages:
    1,928
    Likes Received:
    0
    Location:
    NH
    So I've been trying to figure this out but it keeps stumping me. I need some way to include an AND statement and an OR statement in the same SELECT.

    My problem is this. I want to pick products from the database with a price between two numbers, lets say 20 and 70. So I would go:

    Code:
    SELECT * FROM PRODUCT
    WHERE productprice < 70 AND productprice > 20;
    
    That works fine.

    But now I want to search a few fields, description, model, manufacturer. I am running this on a music database so let's say you search for STRAT. I want to pull any record with STRAT in description, model, OR manufacturer.

    Problem with this is that it is selecting things even if their price isn't within my range - because of the OR.

    Code is like so:

    Code:
    SELECT * FROM PRODUCT
    WHERE productprice < 70 AND productprice > 20
    AND description LIKE('%strat%')
    OR model LIKE('%strat%')
    OR manufacturer LIKE('%strat%');
    
    I can't use all ANDs since strat might be in the description but not in the model field then it wouldn't grab the record. I can't use nested queries because my server hasn't upgraded to mySQL 4.1 yet.

    Any ideas?

    Thanks.
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Brackets are your friend. One before description and one before the ;
     
  3. Slid.

    Slid. I'm a guy.

    Joined:
    Oct 25, 2001
    Messages:
    1,928
    Likes Received:
    0
    Location:
    NH
    Dude, you rock!
     
  4. -RUNNR

    -RUNNR Insanely truthful

    Joined:
    Jan 8, 2002
    Messages:
    343
    Likes Received:
    0
    Location:
    Abilene, TX
    Slid, don't make me beat you... (aka, I've told you this before via aim) :big grin:
     
  5. Slid.

    Slid. I'm a guy.

    Joined:
    Oct 25, 2001
    Messages:
    1,928
    Likes Received:
    0
    Location:
    NH
    You suck - you wouldn't have known about the brackets! :fawk:
     

Share This Page