CODEWITHSUNDEEP
mysql
TV-C-1-5
TV-C-1-5

Reputation: 714

MySQL WHERE LIKE AND OR

I have a database app I'm working on for car products.

I want the returned products results to match the conditions of:

I am using:

SELECT *
FROM `PRODUCTS`
WHERE `MAKE` LIKE '%CHEV%'
AND `CATEGORY` LIKE '%BUMPERS%'
AND `OPTIONS` LIKE '%TOW HOOKS%'
OR `OPTIONS` LIKE '%LIGHT MOUNT HOLES%'

When I use this it works but it is producing results from different MAKES as well. So it is returning products from lets say FORD as well... I don't want that.

I want the MAKE and CATEGORY to be strict (they must match) but the OPTIONS can be one or both - or maybe even three or four different options - but at least one option must match.

The OPTIONS filed is a string separated by pipe characters i.e. LIGHT MOUNT HOLES|TOW HOOKS|FOUR DOOR, and I don't want to change this.

If I use the above query with just one OPTION in the search:

 AND `OPTIONS` LIKE '%TOW HOOKS%'" or just "AND `OPTIONS` LIKE '%LIGHT MOUNT HOLES%'

... it works perfectly. However, if I try to search for multiple OPTIONS, it starts bringing back products from different MAKES.

I have also tried:

SELECT *
FROM `PRODUCTS`
WHERE `MAKE` LIKE '%CHEV%'
AND `CATEGORY` LIKE '%BUMPERS%'
OR `OPTIONS` LIKE '%TOW HOOKS%'
OR `OPTIONS` LIKE '%LIGHT MOUNT HOLES%'

And have the same problem of multiple MAKES showing up.

Upvotes: 3

Views: 43300

Answers (3)

Isaac
Isaac

Reputation: 2721

SELECT *
FROM `PRODUCTS`
WHERE `MAKE` LIKE '%CHEV%'
AND `CATEGORY` LIKE '%BUMPERS%'
AND (`OPTIONS` LIKE '%TOW HOOKS%'
    OR `OPTIONS` LIKE '%LIGHT MOUNT HOLES%'
    OR ...)

This will match at least one option.

If you also want to return cars with NO options matched use:

SELECT *
FROM `PRODUCTS`
WHERE `MAKE` LIKE '%CHEV%'
AND `CATEGORY` LIKE '%BUMPERS%'

Edit

Explanation: the difference between this and what you have is that all the LIKE clauses are grouped together. Which means that they are in essence just one condition next to the make and the category. the thing you need to get your head around is that logical AND/OR are not the same as we use them in English. In your query if a car had options like '%LIGHT MOUNT HOLES%' then the condition is met.

Let M (make), C (category), O1 (option1), O2 (option2) denote the 4 clauses you have in your query, and R be the result of evaluating them. Then R = M AND C AND O1 OR O2. if O2 is true, then M, C and O1 are irrelevant. becuase by definition X = A OR B is true if either A or B is true. what you want is R = M AND C AND (O1 OR O2 OR ...). this way R is true only if all of M, C and at least one of the Os is true.

Upvotes: 7

malexmave
malexmave

Reputation: 1300

My educated guess would be that it is the fault of the OR, because it evaluates like (this AND that AND that AND that) OR something. You could confirm that by checking if all returning different makes contain the criteria from the OR.

As for solving it: You can use brackets: this AND that AND (that OR something_else).

Upvotes: 1

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324630

What you are doing is comparable to having:

3 * 4 + 5

and expecting 27. But because of the order of precedence of operators, the answer is 17.

You can "fix" this and get 27 like so:

3 * (4 + 5)

The exact same applies to many programming languages, and MySQL is no exception.

Put your OR options in parentheses so they get treated as a group.

Upvotes: 3

Related Questions