PHP: Apply a certain summer value at SQL SELECT

[jules]

There's an Internet store. I'll take from one MySQL table the ID number of a certain category of goods with a category iidi, then I'll take their prices from another table using LEFT JOIN and sort through ORDER by the price field:

SELECT a.product_id, b.product_price FROM table_product_categories as a WHERE category_id = $category_id
LEFT JOIN table_product_prices as b ON b.product_id = a.product_id
ORDER BY b.product_price

At first glance, it's simple, but it's costly. TREATMENT:

1. Some goods in the category do not have a price, i.e. in the table. table_product_prices There's no corresponding price recording for their. Some prices are equal 0 or a little more than zero. 1 e.g. 0.01♪ In these cases, such goods are not on the page at all.

2. Field product_price It's the original price. The second problem is that some products have a new price in the field. product_override_price Table table_product_prices (sighs)product_price in such a case, it acts as an old price, and for some products, a discount is shown in the field. product_discount (in the same table). In such cases, sorting ORDER BY b.product_price incorrect as the field of the original price is performed product_price Not prejudiced product_override_priceNo product_discount♪ We need to be sorted at the final price, and it's calculated at the summer and there's no field in the table.

I don't know.

To solve problem 1: If the goods have no price, or the price is equal. 0or more 0 less 1 Then you have to take a summer at SQL-Request (if possible) to take the field. product_price value 1

To solve problem 2: to take into account the new price and the discount. table_product_prices One more field. b.product_override_price and b.product_discount and further if the field is b.product_override_price more than 0 (in default equals 0) then we sort it instead. product_priceand if the value of the field product_discount for some product, too. 0 e.g. 5 (laughs) 5%) then from the original price product_price Let's take the discount. 0.05 (*5%).

Maybe we need to set up some kind of "time" field for the summer. final_price (if possible) and then, in the case of the first problem, assign a value to it 1and in the second case, calculate the final price and then sort through ORDER BY final_price

[kalena]

I think the request should look like this:

SELECT a.`product_id`,
(CASE
    WHEN b.`product_override_price` > 0 THEN b.`product_override_price`
    WHEN b.`product_discount` > 0 THEN  b.`product_price` - (b.`product_discount` * b.`product_price` / 100)
    WHEN b.`product_price` < 1 THEN 1
    ELSE b.`product_price`
END) AS final_price
    FROM `table_product_categories` as a
    LEFT JOIN `table_product_prices` as b ON b.`product_id` = a.`product_id`
    WHERE `category_id` = $category_id
ORDER BY final_price

UPD: I checked this request on the test database, it works. Reference to the working example: http://sqlfiddle.com/#!9/aebc0dd/19/0