CODEWITHSUNDEEP
sqlmysqldatabase
tim-men
tim-men

Reputation: 23

how to modify this particular query?

SELECT * FROM foobar
  WHERE userid != '100' AND col1 REGEXP '[[:<:]]test[[:>:]]'
     OR userid != '100' AND col2 REGEXP '[[:<:]]test[[:>:]]'
     OR userid != '100' AND col3 REGEXP '[[:<:]]test[[:>:]]'

This query is working fine for me. It will filter basically on two criteria

  1. where col1 or col2 or col3 have "test" and
  2. userid is not 100.

I have another col4 which I want that, other than the above two condition, it must filter out those results where col4 = 'y'

How should I modify this above query?

Upvotes: 2

Views: 68

Answers (5)

Brandon
Brandon

Reputation: 69953

Something like this? You could also separate out the userid != '100' since it is common to the three checks.

SELECT * 
FROM foobar 
WHERE userid != '100' 
  AND (col1 REGEXP '[[:<:]]test[[:>:]]' 
       OR col2 REGEXP '[[:<:]]test[[:>:]]' 
       OR col3 REGEXP '[[:<:]]test[[:>:]]' ) 
  AND col4 != 'y'

Upvotes: 1

VoodooChild
VoodooChild

Reputation: 9784

SELECT * FROM foobar WHERE 
(userid != '100') AND 
(col1 REGEXP '[[:<:]]test[[:>:]]' OR userid != '100' OR col2 REGEXP '[[:<:]]test[[:>:]]' OR col2 REGEXP '[[:<:]]test[[:>:]]')
AND col4 <> 'y'

Upvotes: 0

Chandu
Chandu

Reputation: 82893

Try this:

SELECT * 
    FROM foobar 
 WHERE  col4 = 'y'
   OR   (
                userid != '100' 
                 AND (
                                col1 REGEXP '[[:<:]]test[[:>:]]' 
                                OR
                                col2 REGEXP '[[:<:]]test[[:>:]]' 
                                OR 
                                col2 REGEXP '[[:<:]]test[[:>:]]'
                            )
                )

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

SELECT * 
    FROM foobar 
    WHERE userid != '100' 
        AND (col1 REGEXP '[[:<:]]test[[:>:]]' 
          OR col2 REGEXP '[[:<:]]test[[:>:]]'
          OR col3 REGEXP '[[:<:]]test[[:>:]]')
        AND col4 <> 'y'

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 837946

You have an error in your query, You need to change the second col2 to col3. It's easier to see the error if you reformat your code:

SELECT * FROM foobar
WHERE (userid != '100' AND col1 REGEXP '[[:<:]]test[[:>:]]') OR
      (userid != '100' AND col2 REGEXP '[[:<:]]test[[:>:]]') OR
      (userid != '100' AND col2 REGEXP '[[:<:]]test[[:>:]]')

I've also added parentheses to make the evaluation order clear. You can rewrite your query to avoid repeating the expression userid != '100'. Then just add your missing clause:

SELECT * FROM foobar
WHERE userid != '100'
AND col4 <> 'y'
AND (
    col1 REGEXP '[[:<:]]test[[:>:]]' OR
    col2 REGEXP '[[:<:]]test[[:>:]]' OR
    col3 REGEXP '[[:<:]]test[[:>:]]'
)

Upvotes: 2

Related Questions