How do I fix my MySQL select statement to get a correct result

How to get a correct result for my select statement below

SELECT * FROM ads 
WHERE ad_status='1' 
AND ad_region='Location One' OR ad_region='Location Two' OR ad_region='Location Three' 
AND ad_type='For Rent' 
ORDER BY ad_id 
DESC LIMIT 10 

This statement will return to all of ad_type (For Rent, Wanted, For Sale, etc), but I want For Rent only.

I thought the problem开发者_如何学JAVA is on ad_region because have OR. If without 'OR' (single location) the result is correct.

Let me know.

You need to put OR condition into brackets or use IN:

SELECT * FROM ads 
WHERE 
    ad_status='1' 
  AND 
    ad_region IN ('Location One', 'Location Two', 'Location Three')     
  AND 
    ad_type='For Rent' 
ORDER BY ad_id 
DESC LIMIT 10 

Try parenthesizing your OR section:

SELECT * FROM ads 
WHERE ad_status='1' 
AND ( ad_region='Location One' OR
      ad_region='Location Two' OR
      ad_region='Location Three' )
AND ad_type='For Rent' 
ORDER BY ad_id 
DESC LIMIT 10 

AND has a higher precedence than OR as shown here so what you have in your question is equivalent to:

SELECT * FROM ads 
WHERE (ad_status='1' AND ad_region='Location One')
OR ad_region='Location Two'
OR (ad_region='Location Three' AND ad_type='For Rent')
ORDER BY ad_id 
DESC LIMIT 10 

which will give you everything from location 1 with an ad status of 1, plus everything from location 2 plus all rental ads from location 3.

your logic is not entirely clear. You need to enclose the logically subordinate clauses in parentheses in order to let MySQL know what you actually want.

Do you want

ad_status='1'
    AND (ad_region='Location One' OR ad_region='Location Two' OR ad_region='Location Three')
    AND ad_type='For Rent'

?

If you do, you're probably better off using IN(), like this:

SELECT * FROM ads 
WHERE ad_status='1'
    AND ad_region IN ('Location One', 'Location Two', 'Location Three')
    AND ad_type='For Rent' 
ORDER BY ad_id 
DESC LIMIT 10
;

Just put brackets around your 3rd line, i.e.

SELECT * FROM ads 
WHERE ad_status='1' 
AND (ad_region='Location One' OR ad_region='Location Two' OR ad_region='Location Three')
AND ad_type='For Rent' 
ORDER BY ad_id 
DESC LIMIT 10 

You could also use an "in" but I try stay away from these since they are generally quite slow.