Hi All,
I have an issue that I need to resolve that is difficult to explain. I
hope that someone can understand what I*m trying to do and shed some
light on a solution. Here goes.
I have three tables, inventory, which is a list of transactions with
positive and negative values; request, which essentially is a temporary
table that gets deleted after it*s used here; and purchase, which holds
the solution as to whether an item is to be purchased or removed from
inventory,
CREATE TABLE `inventory` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
CREATE TABLE `purchase` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Source` int(11) DEFAULT NULL,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `request` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Required` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
My Inventory and Request tables have data in them like this,
Insert Into `inventory` (Item, Qty)
Values
('Apples',5),
('Bananas',4),
('Cherries',6),
('Apples',-1),
('Bananas',1),
('Cherries',-2),
('Apples',3),
('Bananas',-7),
('Cherries',19),
('Apples',-5),
('Bananas',88),
('Cherries',6);
Insert Into `request` (Required, Qty)
Values
('Apples', 12),
('Bananas', 112),
('Cherries', 5);
Now what I*d like to do is create a single Insert Select query that
creates a record in my purchase table for each of the items in my
request table based on the number of items available in my inventory.
But, if there aren't enough items in the inventory to cover the amount
requested, I need to have a second record for that item in the purchase
table with the qty difference to another source. So based on the data in
the inventory my current totals are,
+----------+----------+
| Item | Sum(Qty) |
+----------+----------+
| Apples | 2 |
| Bananas | 86 |
| Cherries | 29 |
+----------+----------+
and based on the qty of items in my request I would like to have a
purchase table that looks like this,
+--------+----------+-----+
| Source | Item | Qty |
+--------+----------+-----+
| 1 | Apples | 2 |
| 0 | Apples | 10 |
| 1 | Bananas | 86 |
| 0 | Bananas | 26 |
| 1 | Cherries | 5 |
+--------+----------+-----+
with a source of 1 meaning pull the items from inventory and a source
of 0 means purchase them from somewhere else.
Can anyone help me with this?
