Find min max from two different tables

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)

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0