How to add 3 columns of different tables

Question

How to add 3 columns of different tables

Navigation

#1 by (2 votes)
#2 by (0 votes)

3

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

Banks (id, name, initial balance);
Recipes (id, geraParcela, qtdeParcelas)
Recipes_parcels (id, recipe_id, value, date_wind)
Expenses (id, generatesParcela, qtdeParcelas)
Expenses_parcels (id, id_expensity, value, date_wind)

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

The select is 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 add up all the amounts of the revenue that you have received, all portions of the expenses that are paid, and the bank's opening balance.

But if, for example, there is no record in revenue or expense, the result returns NULL .

Is there any other way to add the way I need just with MySQL ?

php mysql sql-select

asked by anonymous 22.04.2014 / 23:29

2 answers

0

Use the function coalesce

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

22.04.2014 / 23:37

Make buttons available dynamically javax.el - Implementation of Glassfish

score 2 · Accepted Answer

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;