# Shantanu's Blog

Corporate Consultant

## MySQL Case Study - 2

You have been provided with the code for parents (for e.g. A01) and their children. The children can have son or daughter. Therefore a code (for e.g. A02) may exist in both columns, parent as well as child. The amount column mentions the income earned from different partnerships.
You need to calculate the total net worth of each person in that family.

CREATE TABLE family (SN INT, parent CHAR(4), child CHAR(4), amount INT);
INSERT INTO family VALUES (1, 'A01', 'A02', '1000'), (2, 'A01', 'A03', '1000'), (3, 'A02', 'A04', '500'), (4, 'A03', null, '1000'), (5, 'A04', null, '200'), (6, 'A05', null, '1500');

If you know the number of levels you need to search in your tree, you can use left joins to print every path in your tree (one join for each level). Try this.

select t1.parent as P1, t2.parent as P2, t3.parent as P3
from family t1
left join family t2 on t1.child=t2.parent
left join family t3 on t2.child=t3.parent;

+------+------+------+
| P1 | P2 | P3 |
+------+------+------+
| A01 | A02 | A04 |
| A01 | A03 | NULL |
| A02 | A04 | NULL |
| A03 | NULL | NULL |
| A04 | NULL | NULL |
| A05 | NULL | NULL |
+------+------+------+

Use this to calculate sums. Note, if the amount is null, change it to zero using the IFNULL function.

select t1.parent, sum(ifnull(t1.amount,0)+ifnull(t2.amount,0)+ifnull(t3.amount,0)) as sum
from family t1
left join family t2 on t1.child=t2.parent
left join family t3 on t2.child=t3.parent
group by t1.parent;
+--------+------+
| parent | sum |
+--------+------+
| A01 | 3700 |
| A02 | 700 |
| A03 | 1000 |
| A04 | 200 |
| A05 | 1500 |
+--------+------+