CODEWITHSUNDEEP
mysqlsql
mk_89
mk_89

Reputation: 2742

IF statement in a WHERE clause

Would it be possible to create an IF statement in a WHERE clause, I would need something like this in order to filter a certain product.

For example if product 005 has a ranking of 100 or less than the product should not show up in my query results

For example...

SELECT b.id, b.rank 
FROM b_products b 
WHERE IF(b.id = '005' AND b.rank < 100, b.id != '005')

Upvotes: 1

Views: 3678

Answers (5)

Carlo Pellegrini
Carlo Pellegrini

Reputation: 5686

The conditions are directly evaluated in the WHERE clause as:

SELECT b.id, b.rank 
FROM b_products b 
WHERE NOT (b.id = '005' AND b.rank < 100)

Upvotes: 0

&#193;lvaro Gonz&#225;lez
&#193;lvaro Gonz&#225;lez

Reputation: 146430

The IF() function expects three arguments, not two:

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.

However, you haven't described anything that cannot be accomplished with a proper boolean expression:

SELECT b.id, b.rank 
FROM b_products b 
WHERE b.id<>'005'
OR (b.id='005' AND b.rank<100)

Upvotes: 1

Saharsh Shah
Saharsh Shah

Reputation: 29051

You have to specify else part in query: 

SELECT b.id, b.rank 
FROM b_products b 
WHERE IF(b.id = '005' AND b.rank < 100, b.id != '005', else condition)

Upvotes: 1

David Martin
David Martin

Reputation: 12248

I think you just need an OR clause.

SELECT 
    b.id, 
    b.rank 
FROM 
    b_products b 
WHERE 
   (b.id = '005' AND b.rank < 100)
or 
    b.id <> '005'

Upvotes: 0

Luca Rainone
Luca Rainone

Reputation: 16458

Simply

WHERE (b.id = '005' AND b.rank >= 100) OR b.id != '005'

Upvotes: 1

Related Questions