Обсуждение: Query to "balance" rows across multiple tables

I have two tables.  One for the quantity of an item I need to buy, and
one for the quantity of an item I already own.  I want to "balance"
these two tables so that I subtract the amount I need from what I
own..

For example:

ToBuy
---------
Item A: 6
Item B: 5
Item C: 3

Owned
---------
Item A: 1
Item B: 5
Item C: 5

The results should be:

ToBuy
----------
Item A: 5 (since I already own one)
Item B: 0 (since I already had five)
Item C: 0 (Since I already had five)

Owned
---------
Item A: 0 (since I used up the one I had)
Item B: 0 (since I used up all five)
Item C: 2 (since I used 3, but have 2 left)

Here's the catch: I want to DELETE any row (in either table) that has
zero quantity, since I no longer need this data (plus I have a CHECK
constraint on this value and require it to be positive anyway)..

Is there actually a way to do this in a SQL function?  Thanks!

Mike

Mike Christensen <mike@kitchenpc.com> wrote:

> [...]
> Here's the catch: I want to DELETE any row (in either table) that has
> zero quantity, since I no longer need this data (plus I have a CHECK
> constraint on this value and require it to be positive anyway)..

> Is there actually a way to do this in a SQL function?  Thanks!

Probably. But I wouldn't take that path: If your function
has a bug, you'll have /no/ record of what went wrong, but
only some numbers that may or may not be correct.

  So I'd rather use a more elaborate table structure where
you can track when you bought/planned to use/used what quan-
tity of items, and then use SUM() & Co. to report what you
own and what you need to buy.

Tim