Find min max from two different tables-CodePudding

I have 3 tables in a MYSQL DB

ORDER

   order_id | order_date
-------------------------
      1     | 2021-09-20    
      2     | 2021-09-21

PRODUCTS

   product_id | product_price
------------------------------
      1       | 30    
      2       | 34
      3       | 39
      4       | 25

ORDER_PRODUCTS

   product_id | order_id | discount_price
------------------------------------------
      1       | 1        |   null
      2       | 1        |   18
      1       | 2        |   null
      4       | 2        |   null

Now I want to know the min and max prices of all products in a specific ORDER record when I give a specific product_id (I need all the ORDERS that have the provided product) group by order_id. I got the required data for this, but here is the tricky part, the ORDER_PRODUCTS table will have the discounted_price for that particular product for that specific ORDER.

So, when computing MIN, MAX values I want discount_price to be prioritized instead of product_price if that product doesn't have any discount_price then product_price should be returned.

EX:

order_id  | min_price                     | max_price
------------------------------------------------------
      1   | 18(p_id=2)(discount price)    | 30(p_id=1)
      2   | 25(p_id=4)                    | 30(p_id=1)

CodePudding user response：

If I understand correctly you are looking for the IfNull()function, you can read about it here

You can simply surround the IfNull()function in the appropriate aggregate function

select o.order_id, 
min(ifnull(discount_price,product_price)), 
max(ifnull(discount_price,product_price)) 
from PRODUCTS p  
inner join ORDER_PRODUCTS op on op.product_id  =p.product_id  
inner join ORDER o on o.order_id = op.order_id
group by o.order_id, p.product_id