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