Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mojam
Starting Member
5 Posts |
 Posted - 2009-09-17 : 01:47:31
|
| Hello experts, I am new in database programming. I am developing a stock inventory. So I created following Tables1. product product_id int primary key name varhcar(50)2. Purchase purchase_id int primary key quantity int, price double, product_id int foreign key of product.product_id3. Sale sale_id int primary key quantity int, price double, product_id int foreign key of product.product_idNow how can I calculate stock? Any idea? Pls help. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-09-17 : 01:51:52
|
[code]select p.product_id, p.name, stock_balance = isnull(r.quantity, 0) - isnull(s.quantity, 0)from product p left join ( select product_id, quantity = sum(quantity) from purchases group by product_id ) r on p.product_id = r.product_id left join ( select product_id, quantity = sum(quantity) from sale group by product_id ) s on p.product_id = s.product_id[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
|
|
|
Query