sqlmysqlsql-order-byinner-join

I don't know How to do this query , I only could find the volume of carton 10


Product table

## Product_id ## ## Len ## ## Width ## ## Height ##
239 350 300 100
240 80 80 150

This is a sample product table , I have given you the relevant columns.This actual table has 60 rows of information like this.

Order_items table

## Order_id ## ## Product_id ## ## Product_quantity##
10001 237 1
10002 240 3
10003 247 4

This is a sample order_items table where product quantity is no of items sold for that particular order_id.

Carton table

## Carton_id ## ## Len ## ## Width ## ## Height ##
10 600 300 100
20 80 80 150

The carton_id 10 has the actual values of length, width and height.

The query is: Write a query to display order_id and volume of the biggest order (in terms of volume) that can fit in carton id 10 . Expected 1 row in final output.

I am not able to do more than this, it's too confusing. Please help

select (c.len*c.width*c.height) as Max_volume 
from carton c 
where c.carton_id = 10;

Solution

  • If there always is a single product in each order, as shown in your sample data, then one option is to join and filter.

    select o.order_id, p.len * p.width * p.height volume
    from order_items o
    inner join products p on p.product_id = o.product_id
    inner join cartons c on c.len >= p.len and c.width >= p.width and c.height >= p.height
    where c.carton_id = 10
    order by volume desc limit 1
    

    The query filters orders on products that fit in a carton 10 (meaning that the three dimensions of the product are smaller than those of the carton), then sorts by descending volume and retains the first row only.