Re: Again:How to create the SELECT script?

Hi Violin,

Below is your answer,

SELECT a.acc_no, a.acc_name, sum(b.amt) from table1 a, table2 b
where a.acc_no = b.acco_no (+)
group by a.acc_no, a.acc_name    

Violin <violin.hsiao_at_mail.pouchen.com.tw> wrote in article <35d22dd0.930112_at_news.twsc.pouchen.com.tw>...
> Hello,
> I think there is some misunderstanding in my last post.
>
> 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!
>
> So my 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 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 Sun Aug 16 1998 - 03:31:03 CDT