I have 3 tables e.g. emptable, tax (1:m with emptable using empid), deductions (1:m with emptable using empid)
========== emptbl ==========
empid, totaltax, totaldeductions...
001, 100, 50
========== tax ========== taxes can be 1 line item per empid or many line items
empid, date, tax_line_item, tax, tax_type...
001, 12-15-2021, 1, 25, A
001, 12-15-2021, 2, 30, B
001, 12-15-2021, 3, 25, C
========== deductions ========== deductions can be 1 line item per empid or many line items
empid, date, ded_line_item, deduction, deduction_type...
001, 12-15-2021, 1, 12.5, W
001, 12-15-2021, 2, 12.5, 401
001, 12-15-2021, 3, 20, I
001, 12-15-2021, 4, 5, B
Desired result
========== RESULT ==========
empid, totaltax, totaldeductions, taxes (as array), tax_type , deductions (as array), deduction_type
001, 100, 50, 25, A, 12.5, W
30 B, 12.5, 401
25 C, 20, I
5, B
If I use the emptable and tax, I would get 1 row and the taxes column as array, which is what I want to achieve but when I join the 3 tables and use array_agg to get the RESULT#2, i get 12 rows (1x3x4) instead of just 4 rows total.
Is there a way a could get the desired result. I appreciate any leads on how to make it happen.