0

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
Sean
  • 51
  • 5

1 Answers1

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
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for your answer althought this can't solve 'layable' issue. Good tip. – Sean Feb 13 '21 at 03:17
  • 1
    The OP reposted the question, and posted his own attempt. From that attempt, we see that even for non-layable items (which he also explains better in the other post), the product can be **rotated** within the box. So least of (width, depth) of product must be compared to least of (width, depth) of box, and greatest to greatest. –  Feb 13 '21 at 16:57