MySQL - conditional query, result on statement

I want to construct a query like this:

SELECT * FROM t1 WHERE visible='yes' AND IF (sale = 'yes', 开发者_如何学Cquantity > 0)

It is my goal is to receive only the rows from the table with quantity more then 0 only when it is a 'sale' product. I tried IF and CASE statements but it seems that the result of these can only by a string of integer, not a statement.

I am stuck here...

---

If you read his original text " receive only the rows from the table with quantity more then 0 ONLY when it is a 'sale' product"

Since the question is somewhat poorly phrased. The assumption is:

Return ONLY items where SALE='yes' and QUANTITY > 0 and VISIBLE='yes'.

If this is what the user wants, the SQL for is simply:

SELECT * FROM t1 WHERE sale='yes' AND quantity > 0 AND visible='yes'

BTW, if your DB is going to hold much data, using booleans(true/false) instead of varchars is much more efficient in the engine and will save time on selects and inserts.

---

SELECT * FROM t1 WHERE visible='yes' AND ((sale = 'yes' AND quantity > 0) OR sale != 'yes')

---

You are missing the 3rd paramater inthe IF function

SELECT *
FROM t1
WHERE visible='yes'
    AND IF (sale = 'yes', quantity > 0, 1) 

is valid SQL