I am using MYSQL and PHP. I have a table called item. Two of its attributes are price and discounted price.I have SELECT statement as below:
$sql =
'SELECT C.cart_id,I.item_id,C.quantity, I.discounted_price,I.price
FROM cart C, item I
WHERE I.item_id = C.item_id';
I want to modify this sql statement and include a conditional expression such that: if discounted_price is 0, it selects price else, discounted_price is selected. Can conditional expression be used in an SQL statement? Thanks! :)
Try this:
SELECT
C.cart_id,
I.item_id,
C.quantity,
COALESCE(I.discounted_price, I.price) AS price
FROM
cart C,
item I
WHERE
I.item_id = C.item_id
COALESCE selects the first non null value from a list of arguments. That is, when discount_price is NULL, it returns price.
If your 'null' value is 0.00, you can use this:
SELECT
C.cart_id,
I.item_id,
C.quantity,
IF(I.discounted_price > 0, I.discounted_price, I.price) AS price
...
That is, if discounted price is greater than zero, use that, otherwise use the normal price.
In SQL Server that would be:
SELECT C.cart_id,I.item_id,C.quantity, CASE WHEN I.discounted_price IS NULL THEN I.price ELSE I.discounted_price END AS price
FROM cart C, item I
WHERE I.item_id = C.item_id
You could give that a try in mysql.
Something like
SELECT C.cart_id,I.item_id,C.quantity,
IfNull(I.discounted_price,I.price) as Price
FROM cart C, item I
WHERE I.item_id = C.item_id
Use IsNull if you are using MSSQL
