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;
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.