I have two tables table1 and table2, I need hierarchical output by joining of both of the tables.
Table1 contains three categories CAT1, CAT2 and CAT3 with one Foreign key i.e. F_ID which is a primary key of another table table2 which contains one more column i.e.VAL.
TABLE 1
-----------------
CAT1 CAT2 CAT3 F_ID
A a aa 1
A a ab 2
A b ba 3
A b bb 4
B c ca 5
B c cb 6
B d da 7
TABLE 2
-------------------
F_ID VAL
1 4
2 6
3 4
4 1
5 6
6 6
7 9
Now i need the data in the below format, where every category and sub-category contains the total sum of VAL.
Need Data in below Format from the above table
----------------------------------------------
A - - 15
A a - 10
A a aa 4
A a ab 6
A b - 5
A b ba 4
A b bb 1
B - - 21
B c - 12
B c ca 6
B c cb 6
B d - 9
B da da 9
Please help me.