Using a select with UNION or another mysql parameter

Question

Using a select with UNION or another mysql parameter

Navigation

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

4

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

I have two different tables. A table is 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 account, cash flow for a given SUPPLIER .

I'm launching the purchases on tabela tb_compras which in the case is a debit (-) and the payments on tabela tb_compras_historico_pagamentos which in this case are the credits (+) . So tb_compras is the debt and tb_compras_historico_pagamentos is the 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'm not able to do select to get the results of the two tables in a single select and sort by date to be able to list the form of the example above. Remember that the tb_compras_historico_pagamentos table is related to the tb_compras table.

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

Because in the case I need to get all the records of the tb_compras table and the tb_compras_historico_pagamentos table (which is linked to tb_compras) and list all by date order to create the inbox and payments stream. / p>

The select I am trying is this, but to no avail:

SELECT value FROM tb_compras WHERE provider = 'ID_FORNECEDOR' UNION ALL SELECT value FROM tb_compras_historico_pagamento WHERE shopping_id = tb_compras.id

php mysql query union-all

asked by anonymous 08.06.2018 / 19:21

2 answers

How to decrease byte array size representing an image Is it possible to change a String of a method at run time?

score 1 · Answer 1

In this case you need to make an inner join, retrieve the values from the debit history table.

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;

score 0 · Answer 2

For versions of MySQL > = 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;