Oracle:Cursor Procedure: sum 2 tables with join

Question

I have 3 tables.

Table1

|Obj_id|Obj_Name|
----------------
|A     |     AAA|
|B     |     BBB|
|C     |     CCC|

Table2

|Obj_id|Amount1|
----------------
|A     |   1000|
|C     |     20|
|A     |    100|
|B     |     50|
|C     |     10|

Table3

|Obj_id|Amount2|
----------------
|B     |    500|
|C     |     10|
|C     |     40|

Now I need to create a procedure "report" that returns a cursor like below

Report

|Obj_Name|Amount1|Amount2|Obj_id|
---------------------------------
|AAA     |   1100|      0|A     |
|BBB     |     50|    500|B     |
|CCC     |     30|     50|C     |

I googled everywhere but there is no matching answer.

Answer 1

Try this

SELECT 
  T1.Obj_Name
  ,NVL(T2.Amount1,0) AS Amount1
  ,NVL(T3.Amount2,0) AS Amount2
  ,NVL(T1.Obj_id ,0) AS Obj_id
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.Obj_id = T2.Obj_id
LEFT JOIN Table3 T3 ON T1.Obj_id = T3.Obj_id
ORDER BY T1.Obj_Name

SQL FIDDLE DEMO

OUTPUT

Obj_Name  Amount1  Amount2  Obj_id
AAA      1000       0       A
BBB       0        500      B
CCC      20        10       C

EDIT

SELECT 
   T1.Obj_Name AS Obj_Name
  ,NVL(T2.Amount1,0) AS Amount1
  ,NVL(T3.Amount2,0) AS Amount2
  ,T1.Obj_id AS Obj_id
FROM Table1 T1
LEFT JOIN (SELECT Obj_id,SUM(Amount1) AS Amount1 FROM Table2 GROUP BY Obj_id) T2 ON T1.Obj_id = T2.Obj_id
LEFT JOIN (SELECT Obj_id,SUM(Amount2) AS Amount2 FROM Table3 GROUP BY Obj_id) T3 ON T1.Obj_id = T3.Obj_id
ORDER BY T1.Obj_Name;

SQL FIDDLE DEMO UPDATED

OUTPUT

OBJ_NAME AMOUNT1  AMOUNT2  OBJ_ID
AAA      1100       0      A
BBB      50         500    B
CCC      30         50     C