Re: Again:How to create the SELECT script?

Violin wrote in message <35d22dd0.930112_at_news.twsc.pouchen.com.tw>...

>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!
>

Ok, what you need is a outer-join. Look up in SQL manuals for its definition.
Anyway this is what you need:

  1 select table1.acc_no, table1.acc_nm, sum(table2.amt)   2 from table1, table2
  3 where table1.acc_no = table2.acc_no (+)   4* group by table1.acc_no, table1.acc_nm SQL> /     ACC_NO ACC_NM SUM(TABLE2.AMT)

---------- -------------------- ---------------
        10 Training cost                    600
        20 Traffic cost                     200
        30 Medical cost                    5000
        40 Sale cost
        50 Manage cost                     1000
        60 Telecom cost

6 rows selected.

The plus sign (+) makes the difference between the inner-join and the outer join.
Basically you tell the DB engine that you want to include all of table2 plus the
inexistent rows that match table1.

Best Regards,

Joćo Prates
jmmp_at_mail.telepac.pt

Visit the Expo98, the last world exposition of this century, at Lisbon - Portugal Received on Wed Aug 12 1998 - 21:20:13 CDT