Items And Boxes

The problem: find all the boxes that an item
can fit into. The only rule is that the item's dimensions (length,
width, height) must be smaller (or equal) to the boxes dimensions.

The solution:

If you absolutely cannot enforce length <= width <= height in the
database, use OLAP functions.

select boxId, itemId from(
select boxId, dim, row_number() over(partition by boxId order by dim
desc) rn from(
select boxId, length dim
union all
select boxId, width dim
union all
select boxId, height dim) t
) box_dim
join
select itemId, dim, row_number() over(partition by itemId order by dim
desc) rn from(
select itemId, length dim
union all
select itemId, width dim
union all
select itemId, height dim) t
) item_dim
on box_dim.rn = item_dim.rn and box_dim.dim >= item_dim.dim
group by boxId, itemId
having count(*)=3


on SQL 2000 you can use subqueries instead of row_number().