CODEWITHSUNDEEP
mysqlif-statementcase
klaaz
klaaz

Reputation: 551

MySQL - conditional query, result on statement

I want to construct a query like this:

SELECT * FROM t1 WHERE visible='yes' AND IF (sale = 'yes', quantity > 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...

Upvotes: 0

Views: 199

Answers (3)

rcarver
rcarver

Reputation: 963

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.

Upvotes: 2

The Scrum Meister
The Scrum Meister

Reputation: 30111

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

Upvotes: 1

davin
davin

Reputation: 45525

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

Upvotes: 1

Related Questions