Isolating the OR part of a SQL query

Databases
1

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

That query works fine.

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)