Condition in Mysql - CASE X = Y THEN Where - Is it possible?

Asked

Viewed 104 times

1

I need to fetch the products from my site.

In it, there are two types: Physical and digital. I would like if the products were physical, to show only those above 0 in stock. If it is digital, there is no need for this stock Where.

I’m doing it this way, but it’s obviously giving Syntax Error.

SELECT * FROM sistema_produto p 
WHERE proAtivo = 'S' 
AND proExcluido = 'N' 
AND (
    CASE `proTipo` = "F" THEN proEstoque > 0
)

Where am I going wrong?

mysql

1 answer

2


The CASE is very versatile, and can be used in many contexts, but in your specific situation it is not necessary or appropriate.

With the use of AND and OR already resolves:

SELECT * FROM sistema_produto p 
WHERE proAtivo = 'S' 
AND proExcluido = 'N' 
AND ( `proTipo` != "F" OR proEstoque > 0 )

That is to say:

  • if the proTipo nay for F, always shows;

  • or, if it’s F, shows only if proEstoque is greater than zero.

Note: in Mysql and Mariadb, whenever necessary, has the function IF( condição, valor se verdadeiro, valor se falso ) which is more "elegant" than the CASE (but less portable).

https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#function_if

  • Perfect! Thank you very much!

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.