Mysql join non-unique columns

Question

I have two tables, want to join them by an non unique column.

CREATE TABLE `order_item` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `soi` char(1) DEFAULT NULL,
  `product` char(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `inventory` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `inventory` char(1) DEFAULT NULL,
  `product` char(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `order_item` (`id`, `product`)
 VALUES
    (1,'aaaa'),
    (2,'aaaa'),
    (3,'aaaa'),
    (4,'bbbb'),
    (5,'cccc');

INSERT INTO `inventory` (`id`, `unitid`, `product`)
VALUES
    (1,'aa-002','aaaa'),
    (2,'aa-00e','bbbb'),
    (3,'aa-00w','cccc'),
    (4,'aa-00d','aaaa'),
    (5,'aa-003','cccc'),
    (6,'aa-003','bbbb');

-------------------------------------------------------------

order_item table
----------------
|id |product|
|1  | aaaa  |
|2  | aaaa  |
|3  | aaaa  |
|4  | bbbb  |
|5  | cccc  |

inventory table
---------------
|id| unitid | product|
|1 | aa-002 | aaaa   |   
|2 | aa-00e | bbbb   |   
|3 | aa-00w | cccc   |   
|4 | aa-00d | aaaa   |   
|5 | aa-003 | cccc   |   
|6 | aa-004 | bbbb   |  

i'm trying to get all the orders that can fulfill by inventory like below

|order_item_id| product | inventory_id | unit_id | product|
| 1           | aaaa    | 1            | aa-002  | aaaa   |
| 2           | aaaa    | 4            | aa-00d  | aaaa   |
| 4           | bbbb    | 2            | aa-00e  | bbbb   |
| 5           | cccc    | 3            | aa-00w  | cccc   |

I tried with inner join + group by like below its only returns one match from each table

select * from order_item
inner join `inventory` on order_item.product = inventory.product
group by inventory.product

Answer 1

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