Get the same result as a query with join

Question

Get the same result as a query with join

Navigation

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

0

I have already researched some forums, looked at my books and searched other sources, but I did not find anything that solved 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).

However, I need to list data from another table, so if I do any of the joins below, the result is "$ 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 '00: 00: 00' time.

Structure of tables:

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

Thank you in advance.

mysql join sql-select select

asked by anonymous 22.01.2018 / 20:41

3 answers

Double click on Grid of a child form, it triggers cellClick of the parent form grid Android mode twelve does not return app in background

score 0 · Answer 1

In this case, if I understood your goal correctly, one way is to do a SELECT by having the table of items as the main and only SELECT data source. Open value, search with a SUB-SELECT. It will lose performance, but in a single SQL, that's the way.

You can still try to apply a GROUP BY.

score 0 · Answer 2

According to Andrey's answer, the SUB SELECT returns the same data as the current SELECT. In this way, I keep changing the values of the result, since I need to search the data in the 'negotiation_parcela' table, and add to the 'negotiacao_parcela_item'.

Only in the presence of the join, wherever it is, that the values are changed. The same is for GROUP BY, which I replaced with WHERE and listed all the dates.

score 0 · Answer 3

Good morning. I'm not sure I got it right. If you make the select without join, it returns $ 125,482.26. If you do select with join, it returns another value, but should return $ 125,482.26. Is that it?

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

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';

And to solve the problem once, you need another field (I do not know if you have jah) to group the parcels and the items of the parcel of a negotiation. So you can join.

I did not test on my machine, so maybe some comma is missing, 'as' or something similar.