How to add 3 columns of different tables

Question

I am developing a financial system. Today we have the following situation:

• Banks (id, name, outstanding);
• Recipes (id, geraParcela, qtyParcelas)
• Receitas_installments (id, id_recipe, value, expiration date)
• Expenses (id, geraParcela, qtParcelas)
• Instalments (id, id_expense, value, maturity)

I made a SELECT in the Mysql to add up the column saldoInicial + valor(receita) - valor(despesa).

The select was as follows:

SELECT SUM(rp.par_valor) + SUM(b.ban_saldoInicial) - SUM(dp.des_valor) AS total FROM bancos b  
INNER JOIN receitas r ON (b.id_banco = r.id_banco)
INNER JOIN receitas_parcelas rp ON (r.id_receita = rp.id_receita)
INNER JOIN despesas d ON (b.id_banco = d.id_banco) 
INNER JOIN despesas_parcelas dp ON (d.id_despesa = dp.id_despesa) 
WHERE id_banco = ID AND rp.par_status = 1 AND dp.par_status = 1

In the SELECT above, you should sum up all the amounts of the receipts you have received, all the instalments of expenses you have paid and the bank’s initial balance.

Only if, for example, there is no record of income or expenses, the result comes back NULL.

Is there any other way to add up the way I need to with Mysql?

Answer 1

I think the following query, using Subqueries, works:

SELECT b.ban_saldoInicial + (
    SELECT sum(rp.valor)
    FROM receitas r
    INNER JOIN receitas_parcelas rp ON rp.id_receita = r.id_receita
    WHERE r.id_banco = ID
    AND rp.par_status = 1) - (
    SELECT SUM(dp.valor)
    FROM despesas d
    INNER JOIN despesas_parcelas dp ON dp.id_despesa = d.id_despesa
    WHERE d.id_banco = ID
    AND dp.par_status = 1)
FROM bancos b
WHERE id_banco = ID;

Answer 2

Use the Function coalesce

SELECT SUM(coalesce(rp.par_valor,0)) + ...