SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Ireland
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Isolating the OR part of a SQL query

    Hi,

    I have a property database that the user can search for various criteria: city, county, price etc.

    There are some properties with a price of zero, where zero means that the price just isn't listed. So it really should be returned in any search regardless of the price the user has selected.

    Does anybody know how I should structure my query so that the min, and max price specified by the user is searched, but the properties with price of zero are also returned?

    I tried something like this:

    SELECT * FROM p WHERE city='xyz' AND ((price >= 100000 AND price <=500000) OR price=0)

    But that didn't work.

    Any help is greatly appreciated.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    That query works fine.

    Code:
    mysql> create table p (city text, price int);
    Query OK, 0 rows affected (0.08 sec)
    
    mysql> insert into p values ('xyz', 0);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> insert into p values ('xyz', 200000);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into p values ('xyz', 10000);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> SELECT * FROM p WHERE city='xyz' AND ((price >= 100000 AND price <=500000) OR price=0);
    
    +------+--------+
    | city | price  |
    +------+--------+
    | xyz  |      0 |
    | xyz  | 200000 |
    +------+--------+
    2 rows in set (0.00 sec)


Bookmarks

Posting Permissions

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