Query data from two different tables and list without merging data

Asked

Viewed 533 times

0

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

I have two different tables. One table is tb_compras(id, nfe, value, date) and then I have another also tb_compras_pagamentos(id, id_compra, valor_pago, date).

I need to create an equal bank account. I am launching purchases and payments. Purchase is a debit and deposit a 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 being able to select to take the results of the two tables in a single query and sort by date to be able to list like this. Remembering that the tb_compra_pagamentos table is related to purchases.

I thought to use this query, but is giving error in select:

SELECT valor_total_pedido AS valor FROM tb_pedido_compra WHERE fornecedor = '$this->id'  UNION ALL  SELECT valor FROM tb_pedido_compra_historico_pagamento  WHERE pedido_compra = tb_pedido_compra.id
php mysql query
  • 2

    Where exactly the columns credito and debito enter the tables tb_compras and tb_compras_pagamentos?

  • select c.id, c.nfe, c.valor, c.data,p.id, p.id_compra,p.valor_pago,p.data, p.id from tb_compras_pagamentos Inner Join tb_compras on c.id = p.id_compra;

  • In this case I think you will have to use UNION so Voce UNE the data of table A and B without adding, and order by will understand that it is a single data block

  • In the case of the SHOPPING table is the debit and the PAYMENTS table the credits

  • I thought of something like this, but it is giving error in select: <pre>SELECT valor_total_order AS value FROM tb_pedido_purchase WHERE supplier = '$this->id' UNION ALL SELECT value FROM tb_pedido_shop histori_payment WHERE requested buy_purchase = tb_pedido_purchase.id</pre>

  • fornecedor is a field of tb_compras? by his description, no (that would be the mistake?)

  • the question is very confusing... you say you want to join the tables "tb_compras" and "tb_compras_pagamentos", but the query you thought of using involves other tables of completely different name, namely "tb_pedido_compra" and "tb_pedido_compra_historico_pagamento". You can show an example of the data of each table to give an idea of the problem?

Show 2 more comments

2 answers

0

If understood correctly, just join the two tables and give a order by desc.

select * 
from tb_compras tc
join tb_compras_pagamentos tcp on tc.id = tcp.id_compra
order by tc.data desc

detail: by the header of the example that showed, I believe that there is another table(s) and/or other fields in which you spoke; there is, for example, no distinction between debt and credit in the table tb_compras_pagamentos to be displayed in the query

  • Join I do the field union, I don’t want that. I don’t know if you understand, but the purchase table is the debt and what I do in the payments are the credits.

  • Then awaiting.

  • fornecedor is a field of tb_compras? by his description, no (that would be the mistake?)

  • supplier is a yes field... the error that shows is that Unknown column 'tb_pedido_compra.id' in 'Where clause'

  • then.. in the tb_pedido_compra there is no field id.. how will you manage it? BS: this table is not one of the ones you listed in the question..

0

SELECT SUM(debito) debito,
       SUM(credito) 
FROM
(  
SELECT valor_total_pedido AS debito,
       0 credito 
FROM tb_pedido_compra 
WHERE fornecedor = '$this->id'  
UNION ALL  
SELECT 0 debito,
       valor credito
FROM tb_pedido_compra_historico_pagamento  
WHERE pedido_compra = tb_pedido_compra.id
/*AQUI FALTA O "WHERE" DO fornecedor = '$this->id'*/
) VIRTUAL

This solution allows grouping as well.

Browser other questions tagged php mysql query

You are not signed in. Login or sign up in order to post.