CODEWITHSUNDEEP
mysql
spen123
spen123

Reputation: 3524

Multiple conditions mySql

I am trying to find all the rows that do no contain an of three strings, "Canada","USA" and "United States" and here is what I have

SELECT 
    * 
FROM 
    `Table` 
WHERE 
    `address` LIKE "%United States%" = 0
    OR `address` LIKE "%USA%" = 0
    OR `address` LIKE "%Canada%" = 0

So if it has either "Canada" or "United States" or "USA" I don't what it to show up. It works for if there is one like this

 SELECT 
    * 
FROM 
    `Table` 
WHERE 
    `address` LIKE "%United States%" = 0

Then I get all that don't have "United states" but it doesn't work with all three?

Thanks

Upvotes: 0

Views: 25

Answers (2)

mrcheshire
mrcheshire

Reputation: 535

First of all, I think you should use NOT LIKE instead of LIKE = 0, but the main issue is that you're using an OR for negative conditions, which doesn't give you the result you want. You should use ANDs.

WHERE 
`address` NOT LIKE "%United States%"
AND `address` NOT LIKE "%USA%"
AND `address` NOT LIKE "%Canada%"

You were getting unexpected results because if that's how OR works. If it matched any of the three cases in your condition, even if it didn't match the other two. So strings with 'USA' in them failed to match the LIKE "%USA%" condition, but they did match the other two, and since at least one of your OR conditions matched, it returned that result.

With AND instead of OR, you should get the result you expect.

Upvotes: 2

Eugene Stamp
Eugene Stamp

Reputation: 158

WHERE $targeted_column != '$prohibited_words'

Upvotes: 0

Related Questions