MySQL Forums
Forum List  »  Newbie

IF ELSE statement within Select query
Posted by: Sahand S.
Date: June 23, 2008 10:00AM

I tried researching this topic but I am new to MySQL and was unable to find answers, I could understand.

I need to pull some product information and also calculate the subtotal by multiplying the number of units selected by the price.
My problem is that I want to add a volume price for some items. So if volqty is 5 and the visitor wants more than 5 items they would be charged a lower price, else just use the normal price.

After a quick search I found some examples of CASE and here is what I wrote:
(Could anyone tell me if this is ok and/or if there is a simpler way to do the same.)

$query_string = "SELECT products.product_id,
CASE WHEN products.volqty > 0 AND products.unit >= products.volqty THEN products.volprice ELSE products.price END * shopping_cart.quantity AS subtotal
FROM shopping_cart INNER JOIN products
ON shopping_cart.product_id = products.product_id
WHERE shopping_cart.cart_id = '$cartId'";

THANK YOU in advance, and I am sorry if this has already been covered.

Options: ReplyQuote

Written By
IF ELSE statement within Select query
June 23, 2008 10:00AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.