How to fix my MySQL query to get the correct result

Question

How to get the right 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 expression will return to all ad_type("Rent", "Required", "Sale", etc.), but I want only For Rent.

I thought the problem is ad_regionbecause it has OR. If without "OR" (single location) the result is correct.

Tell me.

Answer 1

Try copying the section ORin brackets:

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 

ANDhas a higher priority than ORas 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 

, 1, 1, , 2 3.

Answer 2

OR 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 

Answer 3

Your logic is not entirely clear. You must enclose the logical subordinate clauses in parentheses so that MySQL knows what you really want.

Do you want to

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 this, you are probably better off using IN(), for example:

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
;

Answer 4

Just put brackets around your third 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 can also use "in", but I try to stay away from them, as they are usually quite slow.