Thread: Query problem

Query problem

From
"JAMES"
Date:
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



Re: Query problem

From
"JAMES"
Date:
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
>
>



Re: Query problem

From
"JAMES"
Date:
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