Query problem
I have 3 tables, I want to show current stock quantity for each item.
Table item
<item_id> <item_name> <cost>
<selling_price>
P001 A4 Paper 10 12
P002 Pen 2
3
P003 Eraser 1 2
P004 Book 20 25
Table stock_out
<item_id> <qty> <date>
P001 4 11-NOV-00
P002 2 02-NOV-00
Table stock_in
<item_id> <qty> <date>
P001 10 01-OCT-00
P002 10 01-AUG-00
P003 10 01-JUL-00
P004 10 01-AUG-00
How can I show the following result?
<item_id> <qty>
P001 6
P002 8
P003 10
P004 10
THANKS
James
Thanks You,
Richard and Alexander Medvedev
:-)
JAMES <harleyip@hotmail.com> wrote in message
news:90o10u$da12@imsp212.netvigator.com...
I have 3 tables, I want to show current stock quantity for each item.
Table item
<item_id> <item_name> <cost>
<selling_price>
P001 A4 Paper 10 12
P002 Pen 2
3
P003 Eraser 1
2
P004 Book 20
25
Show quoted text
Table stock_out
<item_id> <qty> <date>
P001 4 11-NOV-00
P002 2 02-NOV-00Table stock_in
<item_id> <qty> <date>
P001 10 01-OCT-00
P002 10 01-AUG-00
P003 10 01-JUL-00
P004 10 01-AUG-00How can I show the following result?
<item_id> <qty>
P001 6
P002 8
P003 10
P004 10THANKS
James
I can't get the expected result.
Actually, I have 5 tables,
I am going to find out current stock quantity of each item for current day,
sysdate.
Thanks!
James
invoice
invoice_no staff_name trans_date
01 Lancy Lam 23-DEC-00
02 Tim Hon 02-DEC-00
03 Jack Wong 11-DEC-00
trans
invoice_no item_id qty
01 P001 1
01 P002 5
01 P033 2
01 P034 1
02 P022 4
02 P034 3
02 P301 1
03 P001 1
item
item_id item_name cost selling_price
P001 Unix Training Package 1000 1200
P002 Unix Guide 100 200
P022 Let us go Europe 100 400
P033 Europe Travel Route 10 100
P034 Europe travel Video Tape 200 300
P301 Unix Installation CD 500 1000
package
package_id sub_item_id
P001 P002
P001 P301
P022 P033
P022 P034
stock_in
item_id trans_date qty
P001 02-JAN-00 15
P002 01-DEC-00 50
P022 05-MAY-00 70
P033 25-SEP-00 50
P034 01-MAY-00 40
P301 12-DEC-99 30
P001 20-DEC-00 10
P034 21-DEC-00 5
P002 17-DEC-00 10