Mysql join non-unique columns
Not sure I understood it correctly, but if you really want to match "first order item with first available inventory" etc. until you get out of "available" units, you have to give each row its "rank" - the first/second/third/.. designation. It is possible to do using user variables (and probably by some other tricks).

http://sqlfiddle.com/#!9/cd6ef/4

select 
 order_item_id, order_item_product, order_item_rn,
 inventory_id, inventory_unit, inventory_product, inventory_rn
from
(select 
 `id` order_item_id,
 `product` order_item_product,
 @r:=CASE WHEN @p &lt;&gt; `product` THEN 1 ELSE @r+1 END AS order_item_rn,
 @p:=product AS pset
from
 (SELECT @r:= 0) s,
  (SELECT @p:= '') c,
  (SELECT *
   FROM order_item
   group by id
   ORDER BY product, id
  ) t1) t3
join
(select 
 `id` inventory_id,
 inventory inventory_unit,
 `product` inventory_product,
 @r:=CASE WHEN @p &lt;&gt; `product` THEN 1 ELSE @r+1 END AS inventory_rn,
 @p:=product AS pset
from
 (SELECT @r:= 0) s,
  (SELECT @p:= '') c,
  (SELECT *
   FROM inventory
   group by id
   ORDER BY product, id
  ) t2)t4
ON
 t3.order_item_product = t4.inventory_product
 AND t3.order_item_rn = t4.inventory_rn;


But you should probably do it at the application level and not in SQL at all. For tables containing lot of rows I expect this solution to be quite slow.