Re: How to create the SELECT script?

 >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 :)

I believe you're looking for an outer join. here's an Access SQL example:

SELECT Table1.Field1, Table1.Field2, Sum(Table2.Field3) AS SumOfField3 FROM Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1 GROUP BY Table1.Field1, Table1.Field2;

I believe in Oracle it would be something like (offhand, I can't remember the exact syntax for an outer join):

SELECT Table1.Field1, Table1.Field2, Sum(Table2.Field3) AS SumOfField3 FROM Table1, Table2 where
 Table1.Field1 = Table2.Field1 (+)
GROUP BY Table1.Field1, Table1.Field2; Received on Wed Aug 12 1998 - 22:35:17 CDT