Using a select with UNION or other mysql parameter

Question

I am developing cash flow from my company and am encountering a difficulty.

I have two different tables. A table is a tb_compras(id, fornecedor, nfe, valor, data) and then I have another table tb_compras_historico_pagamentos(id, id_compra, valor_pago, data).

I need to create an equal bank current account, cash flow of a particular SUPPLIER mine.

I’ll launch the shopping on tabela tb_compras which in this case is a debiting(-) and payments in tabela tb_compras_historico_pagamentos which in this case are the credits(+). Then tb_compras is debit and tb_compras_historico_payments is credit.

Ex:

Data | Descrição        | Crédito | Débito | Total
12/06 Cupom fiscal 02               -100,00 -100,00
13/06 Depósito 233        +80,00            -20,00
15/06 Cupom fiscal 11               -200.00 -220.00
17/06 Depósito 1223       +400,00           +180,00

I am unable to select to take the results of the two tables in a single select and sort by date to be able to list in the form of the example above. Remembering that the table tb_compras_historico_pagamentos is related to the table tb_compras.

I thought to use the select below, but is giving error in select, I do not know if it is the ideal way for it.

Because in case I need to get all the table records tb_compras and table tb_compras_historico_pagamentos(that is linked with tb_purchases) and list all by date order to create the incoming cash flow and payments.

The select I’m trying is this, but unsuccessfully:

SELECT value FROM tb_shopping WHERE supplier = 'ID_FORNECEDOR' UNION ALL SELECT value FROM tb_buys_histori_payment where id_purchase = tb_compras.id

Answer 1

In this case you need to do an Inner Join, recover the values of the history table relative to the debit.

SELECT A.valor as debito, B.valor as recebido FROM tb_compras A INNER JOIN tb_compras_historico_pagamento B ON A.id_compra = B.id_compra;

Answer 2

For Mysql versions >= 8.0,

WITH RECURSIVE Q (rowid, data, fornecedor, credito, debito, saldo) AS
(
    SELECT C.id, C.data, C.fornecedor, HCRED.valor_pago as credito, HDEB.valor_pago as debito, COALESCE(HDEB.valor_pago, 0) + COALESCE(HCRED.valor_pago, 0) as saldo
    FROM      tb_compras C
    LEFT JOIN tb_compras_historico_pagamento HCRED
              ON C.id = HCRED.id_compra AND HCRED.valor_pago > 0
    LEFT JOIN tb_compras_historico_pagamento HDEB
              ON C.id = HDEB.id_compra AND HDEB.valor_pago < 0

    UNION

    SELECT C.data, C.fornecedor, HCRED.valor_pago, HDEB.valor_pago, COALESCE(HCRED.valor_pago, 0) + COALESCE(HDEB.valor_pago, 0) + Q.saldo
    FROM tb_compras C
    JOIN Q ON C.id = Q.rowid + 1
    LEFT JOIN tb_compras_historico_pagamento HCRED
              ON C.id = HCRED.id_compra AND HCRED.valor_pago > 0
    LEFT JOIN tb_compras_historico_pagamento HDEB
              ON C.id = HDEB.id_compra AND HDEB.valor_pago < 0
)
SELECT *
FROM Q
ORDER BY 2, 3;