I have 2 table master coa and coa transaction in MySQL like this,
master : id | coa | id_parent | ----+------+-----------+ 1 | 1 | NULL | 2 | 11 | 1 | 3 | 111 | 2 | 4 | 12 | 1 | 5 | 121 | 4 | 6 | 122 | 4 | transaction : id | debit | credit | ----+-------+--------+ 3 | 0 | 5 | 3 | 0 | 20 | 5 | 10 | 0 | 5 | 15 | 0 | 5 | 5 | 0 | 6 | 0 | 5 |
The depth level child-parent is unlimited. What i want to get is view like this,
id | coa | debit | credit | ----+------+-------+--------+ 1 | 1 | 20 | 30 | 2 | 11 | 0 | 25 | 3 | 111 | 0 | 25 | 4 | 12 | 20 | 5 | 5 | 121 | 20 | 0 | 6 | 122 | 0 | 5 |
How can i accomplish this using SELECT ? so far i only can get debit credit only the lowest depth using GROUP SUM and join. Any suggestion?