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.