0

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
  • What about the row 3 | aaaa | 1 | aa-002 | aaaa (and 3/4 combination too)? Any reason for it not to be in the results? – jkavalik Nov 9 '15 at 11:08
  • @jkavalik it should not be in the result because theoretically there is no inventory for that item – Ntwobike Nov 9 '15 at 11:10
  • 1
    So you want the "first" item to "use" the "first" available inventory and the "second" item to use the "second" inventory etc? That is not a very good task for SQL. You should usually do such things in your application. – jkavalik Nov 9 '15 at 11:13
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.