How to create the SELECT script?

From: Violin <violin.hsiao_at_mail.pouchen.com.tw>
Date: Thu, 13 Aug 1998 00:39:59 GMT
Message-ID: <35da3558.2858616@news.twsc.pouchen.com.tw>

Hello,
My database is Oracle 7.3
I have 2 tables are:TABLE1 is master,TABLE2 is detail. And the data is:

TABLE1:
ACC_NO ACC_NM

10                Training cost
20                Traffic cost 
30                Medical cost
40                Sale cost
50                Manage cost
60                Telecom cost

TABLE2:
ACC_DATE ACC_NO AMT

960101            10                  100
960101            20                  200
960103            30                5000
960105            10                   400
960109            50                1000

I wonder how to return the result:
List all rows of master TABLE1,and count sum(AMT) of TABLE2. But if I use the clause:WHERE TABLE1.ACC_NO=TABLE2.ACC_NO, then I just could get the rows exist in both TABLE1 & TABLE2. So my purpose is not only count sum(AMT) of TABLE2 for each ACC_NO, but also list all the ACC_NO in master TABLE1 whether the ACC_NO exists in TABLE2.

ACC_NO ACC_NM TOTAL

10               Training cost             500
20               Traffic cost                 200
30               Medical cost           5000


"40               Sale cost                         "  -> the 2 ACC_NO


50               Manage cost          1000      don't exist in TABLE2,


"60               Telecom cost                "  ->but I want to see!

I don't know if it possible,cause I try & try & try but still can't find the solution.
Please give me some suggestions for the select statement, I'll very appriciate for your help!
Please Cc to : violin.hsiao_at_mail.pouchen.com.tw,Thank you so much :) Received on Wed Aug 12 1998 - 19:39:59 CDT