Thread: 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