Get MIN, MAX values from a linking table

74
September 24, 2021, at 00:10 AM

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.

READ ALSO
How to avoid duplicate items in PagingAdapter?

How to avoid duplicate items in PagingAdapter?

I have implemented paging3 for my android projectTo avoid duplicated items, I created a DiffUtil

39
WebRTC Force Stereo in Chrome by editing the sdp config

WebRTC Force Stereo in Chrome by editing the sdp config

I'm trying to implement a workaround for what appears to be a known issue in all browsers but firefox where webRTC audio stream is downgraded to mono from stereo

47
get origin url of 301/302 Forward in PHP/Javascript

get origin url of 301/302 Forward in PHP/Javascript

I have a small question: From different Domains I'm forwarding via 301/302 to one distinct target domainOn the target Server I have a running apache

40