[RESOLVED] Mysql sort question

SeenGee

Hi all,

I have a product listing, the main table called products contains most product info each product contains a price, p_price, each product can also have a special offer applied to it, these offers are stored in a special offers table, special_offers. in this table i can track previous specials so that historical orders remain accurate. The current price of the product with an active special offer applied is stored in the special offers table. The problem i have is that i need to sort products by price, this works without special offers but not with offers as my current system relies on the p_price value to sort and therefore ignores the active special price. Adding a join to the special offers table will only return products with special offers and many dont so this isnt suitable either. I need to be able to sort by active special offer price (where this is one) and otherwise by price. I can think of numerous ways to do this by changing the db structure but ideally i dont want to touch this.

the code below is a sample query, i know this is incorrect but hopefully it might make it more obvious what i'm trying to do. thanks.

SELECT `p_id`,`so_showprice` AS `offer_price` FROM `ls_products` LEFT JOIN `ls_special_offer` ON (`so_pid` = `p_id`) ORDER BY `offer_price`,`p_price`

cahva

You could use some IF inside the sql.

SELECT ...,IF(ls_special_offer>0,ls_special_offer,p_price) finalprice FROM ...

This translates to something like this(pseudo):
IF special_offer > 0 THEN finalprice = special_offer ELSE finalprice = price

Then you would sort by that finalprice.

Hope you got what I intended from my mumblings. Im a little sleepy now(3 am in finland now) 🙂

SeenGee

cahva wrote:
You could use some IF inside the sql.
SELECT ...,IF(ls_special_offer>0,ls_special_offer,p_price) finalprice FROM ...
This translates to something like this(pseudo):
IF special_offer > 0 THEN finalprice = special_offer ELSE finalprice = price

Then you would sort by that finalprice.

Hope you got what I intended from my mumblings. Im a little sleepy now(3 am in finland now) 🙂

thanks.

i'm not too familiar with the IF syntax so i'm not sure what you mean exactly, i assume the implication is that i can conditionally determine whether final price is so_showprice or p_price dependent on whether there is a value for so_showprice .. ?

SeenGee

this seems to work actually :

SELECT `p_id` , `so_showprice` , IF( `so_showprice` >0, so_showprice, p_price ) AS `finalprice`
FROM `ls_products`
LEFT JOIN `ls_special_offer` ON ( `so_pid` = `p_id` )
ORDER BY `finalprice`

the syntax seems a little strange to me but it works a treat, thanks a lot.