Outer Join _ SQL

[Reshmi Jacob]

Hello

I have 3 Tables

1. Acc
------------------------
CoCode
AccCode
AccName

2. Trn
------------------------
CoCode
BrCode
AccCode
Amount

3. Opening
------------------------
CoCode
BrCode
AccCode
OpenBalance

RELATIONS :-
* Trn.CoCode = Acc.CoCode And Trn.AccCode = Acc.AccCode
* Opening.CoCode = Acc.CoCode And Opening.AccCode = Acc.AccCode


I want all records from Acc to list out ACCCODE, ACCNAME(may or maynot have values in Table Trn or Opening) with corresponding value of SUM(AMOUNT) from table Trn and SUM(OPENBALANCE ) from table Opening

Group by Brcode - which is not in Acc


Help........... ............


Regards
Reshmi

[Dave44]

well it seems as though you have done most of the hard work already... you just need to write it now.

this should get you started, you can take it from here...

SELECT   a.acccode,
         a.accname,
         SUM( t.amount),
         SUM( o.openbalance)
FROM     acc a, trn t, opening o
WHERE    a.cocode = t.cocode(+)
AND      a.acccode = t.acccode(+)
AND      a.cocode = o.cocode(+)
AND      a.acccode = o.acccode(+)
GROUP BY t.brcode, a.acccode, a.accname