How to make a query to get discriminated values?

Question

I have an application with 4 tables:

Employees -> Companies -> Additionals -> Debts

I need to generate this following salary receipt:

Receipt Example

On this document I have to show all discriminated information as SALARY and its value and fees. And at the end the net value between additionals and debts.

I made this example:

$total_additional= 0;
$total_debt= 0;

// Here in employees contains a join between employees and companies
foreach ($employees as $employee) {

  foreach ($additionals as $additional) {
    if ( $employee->id == $additional->employee_id ) {
     //Print the additional reference
     $total_additional += $additional->value;
    }
  }

  foreach ($debts as $debt) {
    if ( $employee->id == $debt->employee_id ) {
     //Print the debt reference
     $total_debt += $debt->value;
    }
  }

  // Here should come the net value

}

How can I make a query to fetch those results ?

Answer 1

How about using some LEFT JOINs, GROUP BY clause and SUM aggregations to get all data in one query without having to loop in PHP?

SELECT employees.id, employees.name, 
    GROUP_CONCAT(additionals.value SEPARATOR '|') as additionals_values, SUM(additionals.value) as total_additional
FROM employees
LEFT JOIN additionals ON additionals.employee_id = employees.id
GROUP BY employees.id, employees.name;

SELECT employees.id, employees.name, 
    GROUP_CONCAT(debts.value SEPARATOR '|') as debts_values, SUM(debts.value) as total_debt
FROM employees
LEFT JOIN debts ON debts.employee_id = employees.id
GROUP BY employees.id, employees.name;

In php use explode('|'... to have an array of individual values. Net value can be calculated with 3'rd query or in PHP.