How to do select from where x is equal to multiple values?

Question

So I', debugging some code and there is the following SQL query (simplified from what it really is).

SELECT ads.*, location.county 
FROM ads
LEFT JOIN location ON location.county = ads.county_id
WHERE ads.published = 1 
AND ads.type = 13
AND ads.county_id = 2
OR ads.county_id = 5
OR ads.county_id = 7
OR ads.county_id = 9

I'm getting very strange results from the query and I think its because the first OR is negating the AND 's that are before it. So I am getting results back for ads of all types and not just of type 13. Each time the query is called there may be many county's that need looking up or just a few.

Any help on the correct way to go about this would be appreciated.

Answer 1

Put brackets around the "OR"s:

SELECT ads.*, location.county 
FROM ads
LEFT JOIN location ON location.county = ads.county_id
WHERE ads.published = 1 
AND ads.type = 13
AND
(
    ads.county_id = 2
    OR ads.county_id = 5
    OR ads.county_id = 7
    OR ads.county_id = 9
)

Or even better, use IN:

SELECT ads.*, location.county 
FROM ads
LEFT JOIN location ON location.county = ads.county_id
WHERE ads.published = 1 
AND ads.type = 13
AND ads.county_id IN (2, 5, 7, 9)

Answer 2

You can try using parentheses around the OR expressions to make sure your query is interpreted correctly, or more concisely, use IN:

SELECT ads.*, location.county 
FROM ads
LEFT JOIN location ON location.county = ads.county_id
WHERE ads.published = 1 
AND ads.type = 13
AND ads.county_id = 2
AND ads.county_id IN (2,5,7,9)

Answer 3

And even simpler using IN:

SELECT ads.*, location.county 
  FROM ads
  LEFT JOIN location ON location.county = ads.county_id
  WHERE ads.published = 1 
        AND ads.type = 13
        AND ads.county_id IN (2,5,7,9)