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
Best Answer
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
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.