CODEWITHSUNDEEP
phpmysqlcodeigniter
Lucky13
Lucky13

Reputation: 11473

sql query condition

I've a table like this

days    discount    
  3      5.00   
  7     10.00   
10     15.00

Up to 3 day discount price is Rs 5. 4 to 7 price is Rs 10 and so on... I've total no of days.If no of days is < 4 then get 5.00 . if no of days 4 to 7 then get 10.How can I write condition in sql query

Upvotes: 1

Views: 176

Answers (4)

coolgeek
coolgeek

Reputation: 913

You need to use the case construct in your select statement.

SELECT CASE numdays 
    WHEN >= 8 THEN 15.0 
    WHEN >= 4 THEN 10.0 
    WHEN < 4 THEN 5.0
    END AS discount

Upvotes: 0

Nanne
Nanne

Reputation: 64399

Take for instance '6' days. You want to select all discounts where the days in the table are bigger then your 6 (so 7 or 10), and then get the smallest (7).

SELECT discount FROM yourTable
WHERE
days >= '6'
ORDER BY days DESC
LIMIT 1

(As you said '3' has a discount too, I'm assuming that "4 to 7" means including 7. Otherwise, remove the = from the query)

As mentioned in the comments, you should use prepared statements if you can, so you would do something like this:

SELECT discount FROM yourTable
WHERE
days >= ?
ORDER BY days DESC
LIMIT 1

and then bind that ? to an integer.

Upvotes: 2

nybbler
nybbler

Reputation: 4841

You could create and use a prepared statement like the following: 

SELECT discount 
FROM myTable 
WHERE days >= ?
ORDER BY days DESC 
LIMIT 1

Upvotes: 1

Paul Schreiber
Paul Schreiber

Reputation: 12589

SELECT discount FROM mytable WHERE days > XXXX LIMIT 1 where XXX is the number of days you want.

Upvotes: 0

Related Questions