Get the same result from a query with Join

Question

I have researched on some forums, consulted my books and looked in other sources, but I did not find anything to solve my problem.

I have a query that queries the open value in a given table:

select negociacao_parcela.data_pagamento,
sum(IF(negociacao_parcela.pagamento_status_id = 1, negociacao_parcela.valor, 
0)) Aberto from negociacao_parcela
where negociacao_parcela.data_pagamento = '2018-01-22 00:00:00';

which returns me the value "R$ 125,482.26" (at the time of publication of this).

However, I need to list data from another table, so if I do any of the joins below, the result is "R$ 191,702.28" (at the time of this publication).

JOIN:

select negociacao_parcela.data_pagamento,
sum(IF(negociacao_parcela.pagamento_status_id = 1, negociacao_parcela.valor, 0)) Aberto from negociacao_parcela
join negociacao_parcela_item on negociacao_parcela_item.parcela_id = negociacao_parcela.id
where negociacao_parcela.data_pagamento = '2018-01-22 00:00:00';

LEFT JOIN:

select negociacao_parcela.data_pagamento,
sum(IF(negociacao_parcela.pagamento_status_id = 1, negociacao_parcela.valor, 0)) Aberto from negociacao_parcela
left join negociacao_parcela_item on negociacao_parcela_item.parcela_id = negociacao_parcela.id
where negociacao_parcela.data_pagamento = '2018-01-22 00:00:00';

RIGHT JOIN:

select negociacao_parcela.data_pagamento,
sum(IF(negociacao_parcela.pagamento_status_id = 1, negociacao_parcela.valor, 0)) Aberto from negociacao_parcela
right join negociacao_parcela_item on negociacao_parcela_item.parcela_id = negociacao_parcela.id
where negociacao_parcela.data_pagamento = '2018-01-22 00:00:00';

INNER JOIN:

select negociacao_parcela.data_pagamento,
sum(IF(negociacao_parcela.pagamento_status_id = 1, negociacao_parcela.valor, 0)) Aberto from negociacao_parcela
inner join negociacao_parcela_item on negociacao_parcela_item.parcela_id = negociacao_parcela.id
where negociacao_parcela.data_pagamento = '2018-01-22 00:00:00';

Note: All payment dates have the time '00:00:00'.

Table structure:

desc negociacao_parcela;
    id
    negociacao_id
    rps_id
    pagamento_status_id
    parcela
    valor
    data_pagamento

desc negociacao_parcela_item;
    id
    parcela_id
    negociacao_cobranca_id
    cobranca_id
    documento
    data_vencimento
    valor_aberto
    valor_montante
    total
    diasAtraso
    subtotal
    comissao
    jurosCredor
    jurosComissao
    jurosFinal
    protesto
    parcela

Grateful from now on.

Answer 1

In this case, if I understand your goal correctly, one way is to make a SELECT by having the item table as the main and only source of SELECT data. The open value, search with a SUB-SELECT. You will lose performance, but in a single SQL, it is the way.

You can still try applying a GROUP BY.

Answer 2

According to Andrey’s answer, SUB SELECT returns the same data as the current SELECT. This way, it continues changing the values of the result, since I need to fetch the data from the table 'negociacao_parcela', and add to 'negociacao_parcela_item'.

Only in the presence of Join, wherever it is, do the values change. The same goes for GROUP BY, which I replaced with WHERE and listed all the dates.

Answer 3

Good morning. I’m not sure I understand correctly. If you select without Join, returns R $ 125.482,26. If you select with Join, returns another value, but should return R $ 125.482,26. That’s it?

If yes, I think the solution would be query derived.

select negociacao_parcela.Aberto, negociacao_parcela.data_pagamento, negociacao_parcela_item.qualquer_campo
from negociacao_parcela_item
    ,(
       select negociacao_parcela.data_pagamento,
              sum(IF(negociacao_parcela.pagamento_status_id = 1, negociacao_parcela.valor, 0)) Aberto, 
              negociacao_parcela.id_agrupador id_agrupador 
       where negociacao_parcela.data_pagamento = '2018-01-22 00:00:00'
       group by negociacao_parcela.data_pagamento, negociacao_parcela.id_agrupador
     ) as negociacao_parcela
where negociacao_parcela_item.id_agrupador = negociacao_parcela.id_agrupador
  and negociacao_parcela.data_pagamento = '2018-01-22 00:00:00';

To solve the problem once and for all, you need another field (I don’t know if you have) to group the parcels and the items of the parcel of a negotiation. So you can do the Join.

I didn’t test it on my machine, so maybe it’s missing a comma, 'as' or something similar.