How to mount an Inner Join by filtering through a field with minimum value?

Asked

Viewed 357 times

0

I wanted to ride a inner join that brings only the values filtered by a minimum value field

select min(data_vencimento) from pagamento where cod_situacao = 3,
cli.cpf, cli.nome, con.data_inicio, con.data_fim,
sit.situacao, con.valor_mensalidade  from cliente cli 
inner join contrato con on con.cpf = cli.cpf
inner join pagamento pag on pag.COD_CONTRATO = con.COD_CONTRATO
inner join situacao sit on sit.cod = pag.cod_situacao;
oracle
  • 1

    How were your tables made? Where do you get this minimum value?

  • would be a maturity as varchar and payments are automatically generated in the system c#.

  • has payment situation: 1 = open, 2 = paid, 3 past due

  • I managed five payments, left three open, one overdue, one paid. As an example : 1º Due date = 09/07/2017 open 2° Due date = 09/08/2017 open wanted to show only one line with maturity for 09/07/2017

  • @Marcusvinicius , have you studied relational algebra? SQL is a partial implementation of this mathematics, it can help a lot in your queries understand the algebraic part behind all this

1 answer

1


You should do it like this:

SELECT MIN(co.data_vencimento), c.cpf
FROM cliente c
INNER JOIN contrato co ON co.cpf = c.cpf 
INNER JOIN pagamento p ON p.COD_CONTRATO = co.COD_CONTRATO 
INNER JOIN situacao s ON s.cod = p.cod_situacao 
WHERE cod_situacao = 3
GROUP BY c.cpf

Taking into account that the "due date" field exists in the "contract" table and that you want the lowest due date per customer.

If you want it grouped by contract, it should be like this:

SELECT MIN(co.data_vencimento), con.cod
FROM cliente c
INNER JOIN contrato co ON co.cpf = c.cpf 
INNER JOIN pagamento p ON p.COD_CONTRATO = co.COD_CONTRATO 
INNER JOIN situacao s ON s.cod = p.cod_situacao 
WHERE cod_situacao = 3
GROUP BY co.cod

The important thing here is to understand that the "group by" together with the "MIN" aggregation function is what will give you this information.

Abs!

  • A subselect of the data_maturity type = (select min(data_maturity) from ...) does not resolve ?

  • It can solve but you are doing a subquery which brings a greater complexity to your query .. Here is another option for choice, if you prefer to use subselect, I see no problems.

Browser other questions tagged oracle

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