Re: Again:How to create the SELECT script?

Hellow.
I think if you use outer-join, you will see not only the rows existing TAB2 but
alse the rows not existing in TAB2.
The method is very easy. If you want to see all the records of TAB1, then you just add '(+)' operator in the TAB2 side.

this is example)
select a.ACC_NO, sum(AMT) from TAB1 a, TAB2 b where b.ACC_NO(+) = a.ACC_NO
group by a.ACC_NO;

bye.

Violin wrote:

> 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 Tue Aug 18 1998 - 01:41:47 CDT