CODEWITHSUNDEEP
sqlmysql
Binarytales
Binarytales

Reputation: 9728

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

I am debugging some code and have encountered the following SQL query (simplified version):

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 operators that are found before it.

This results in getting results back for ads of all types and not just for the type 13.

Each time the query is called there may be a differnt amount of county entities that need to be looked up.

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

Upvotes: 84

Views: 305784

Answers (3)

Greg
Greg

Reputation: 321588

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)

Upvotes: 167

Ned Batchelder
Ned Batchelder

Reputation: 375534

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 IN (2,5,7,9)

Upvotes: 27

Ruben
Ruben

Reputation: 9120

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)

Upvotes: 15

Related Questions