Menu
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.
533
Note – Use of IN for matching multiple values i.e. TOYOTA and HONDA in the same column i.e. COMPANY. Syntax: SELECT * FROM TABLE_NAME WHERE COLUMN_NAME IN (MATCHING_VALUE1,MATCHING_VALUE2);
The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.
You can specify multiple conditions in a single WHERE clause to, say, retrieve rows based on the values in multiple columns. You can use the AND and OR operators to combine two or more conditions into a compound condition. AND, OR, and a third operator, NOT, are logical operators.
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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
