Binarytales Binarytales - 3 months ago 14
SQL Question

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

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

Put parentheses 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)