Query not working

[hybmg57]

Hi,

 

I have below query which is currently not working properly. It seems like bldgname is not filtering and it comes with all results instead.

Is there anything wrong with below?

 

SELECT * FROM idx WHERE 1 AND listprice >=0 AND listprice <=100000000 AND bedrooms >=0 AND baths >=0 AND totintarea >=0 AND landarea >=0 AND highschool LIKE '%Ahuimanu%' AND bldgname LIKE '1040 Kinau' AND catgnum LIKE '1' AND catgnum LIKE '1' OR catgnum LIKE '2' AND catgnum LIKE '1' OR catgnum LIKE '2' OR catgnum LIKE '2' AND catgnum LIKE '1' OR catgnum LIKE '2' OR catgnum LIKE '2' OR catgnum LIKE '4'

 

 

[waseem]

Your query is missing first where clause. Please see.

SELECT * FROM idx WHERE 1

You must place your OR clauses in small brackets like this, OR operator's precedence is before AND, and thats why check before AND.

 

Hope its the answer to the question.

Thanks

Waseem

 

[hybmg57]

I'm sorry I don't understand what you mean 

 

[ManiacDan]

I think he means:

1)  You don't need WHERE 1.  You need the "WHERE" but the "1 AND" is unnecessary.  Drop it.

 

2)  AND takes precedence over OR.  A query that contains "WHERE col1 = 'abc' AND col2 = '123' OR col3 = '456'", the first two are grouped together by the AND and then that GROUP is ORed with the third item.  You need to wrap your OR clauses in parens, this is what you want:  "WHERE col1 = 'abc' AND (col2 = '123' OR col3 = '456')".  See the difference?  Any group of OR items that are meant to be grouped together must be wrapped in parens like that.

 

Also: 3)  Don't use LIKE for straight comparisons, and don't quote numbers.  Also note that you have a big chain of AND/OR statements with the same CATGNUM repeated over and over.  "AND CATGNUM IN (1,2,4)" is probably what you want there...but I can't tell due to the duplicates and lack of parens.

 

-Dan