How to find the right size box?

Question

This is a hard question and I hope I can get the answer here.

The question is to find the right size box which allow the logistic business to save the money when shipping.

We have 2 tables which are boxes and products.

Boxes table contains each ID and dimensions per box. 'w' for wide, 'd' for depth and 'h' for height. Please assume we have just 3 box samples for our convenience.

Products table includes also product ID, dimensions. Dimensions has the same meaning as boxes table. 'layable' means the product can be packaged not only straight position but also layable position. For instance product 'g' is a fragile bottle not being able to put horizontal position in the box. Thus this is 'n' in layable column.

This question needs to query each product ID with the right size box. The right size box means the product needs to be shipped with box that is the least space.

Hope your kind help. Thanks.

boxes:

size	w	d	h
S	353	250	25
M	450	350	160
L	610	460	460

products:

ID	w	d	h	layable
a	350	250	25	y
b	450	250	160	y
c	510	450	450	y
d	350	250	25	y
e	550	350	160	y
f	410	400	430	n
g	350	240	25	n
h	450	350	160	n
i	310	360	430	n

Expected output:

ID	size
a	S
b	M
...	....
...	....
...	....
g	S

Answer 1

Hmmm . . . I'm not quite sure how "layable" fits in. But you want the smallest box that is as big as or bigger than each dimension. The basic idea is:

select p.*,
       (select b.size
        from boxes b
        where b.w >= p.w and b.d >= p.d and b.h >= p.h
        order by b.size desc -- happens to works because S > M > L
        limit 1
       ) as size
from products p