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
3 | aaaa | 1 | aa-002 | aaaa
(and 3/4 combination too)? Any reason for it not to be in the results?