Get MIN, MAX values from a linking table

Question

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
-------------------------
      1     | 1    
      2     | 1 
      1     | 2    
      4     | 2 

Now I want to know the min and max prices of all products in a specific order when I give a specific product id group by order_id.

EX:

order_id | min_price         | max_price
-----------------------------------------
      1  | 30(p_id=1)        | 34(p_id=2)
      2  | 25(p_id=4)        | 30(p_id=1)

Answer 1

You may use the following:

    SELECT
        op.order_id,
        MIN(p.product_price) as min_price,
        MAX(p.product_price) as max_price
    FROM
        ORDER_PRODUCTS op
    INNER JOIN
        PRODUCTS p ON op.product_id = p.product_id
    GROUP BY 
        op.order_id

EDIT 1

As it pertains to

Now I want to know the min and max prices of all products in a specific order

You may use a where clause to only consider a specific order

    SELECT
        op.order_id,
        MIN(p.product_price) as min_price,
        MAX(p.product_price) as max_price
    FROM
        ORDER_PRODUCTS op
    WHERE
        op.order_id = <insert order id here>
    INNER JOIN
        PRODUCTS p ON op.product_id = p.product_id
    GROUP BY 
        op.order_id

or if it is that you would like to determine the min and max prices for orders that have a particular product, you may modify your where clause as below to use an IN with a subquery

    SELECT
        op.order_id,
        MIN(p.product_price) as min_price,
        MAX(p.product_price) as max_price
    FROM
        ORDER_PRODUCTS op
    WHERE
        op.order_id IN (
             SELECT inc.order_id
             FROM ORDER_PRODUCTS inc
             WHERE inc.product_id = <insert product id here>
        )
    INNER JOIN
        PRODUCTS p ON op.product_id = p.product_id
    GROUP BY 
        op.order_id

or simply by using a having clause with a case statement to filter

    SELECT
        op.order_id,
        MIN(p.product_price) as min_price,
        MAX(p.product_price) as max_price
    FROM
        ORDER_PRODUCTS op
    INNER JOIN
        PRODUCTS p ON op.product_id = p.product_id
    GROUP BY 
        op.order_id
    HAVING 
        SUM(
            CASE WHEN op.product_id = <insert product id here> THEN 1 END
        ) > 0

NB. Please replace <insert product id here> and <insert order id here> with actual values while testing.

View working demo db fiddle

Let me know if this works for you.