Thanks Jay,
I had to make a change to the first part of the query to get the results that I
wanted but your suggestion was definitely what I needed to get to the solution.
Thanks again.
For those that are interested, here's the final solution,
INSERT INTO purchase (Source, Item, Qty)
SELECT 1, totals.Item,if((totals.TotQty -r.Qty)>0,r.qty, totals.TotQty)
FROM request r
JOIN
(SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item) AS totals
ON r.Required = totals.Item
UNION All
SELECT 0, totals.Item, (r.Qty - totals.TotQty)
FROM request r
JOIN
(SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item) AS totals
ON r.Required = totals.Item
WHERE r.Qty > totals.TotQty;
>>> On 8/10/07 at 12:33 PM, in message <[EMAIL PROTECTED]>, Jay Pipes <[EMAIL
>>> PROTECTED]> wrote:
Ed Reed wrote:
> 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?
Try this:
INSERT INTO purchase (Source, Item, Qty)
SELECT
1, totals.Item, r.Qty
FROM request r
JOIN (
SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty <= totals.TotQty
UNION ALL
SELECT
0, totals.Item, (r.Qty - totals.TotQty)
FROM request r
JOIN (
SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty > totals.TotQty;
cheers,
Jay
