Question:
The query is as follows, there are 2 tables:
Pagos
-idPago
-idCliente
-fechaEnQuePaga
-fechaVencimiento
Cliente
-idCliente
-nombre
-apellidoPaterno
-apellidoMaterno
-correo
idCliente
is a foreign key. Assuming the tables have the following data:
Payment Table:
1 | 1 | 2018-02-23 | 2018-03-23
2 | 2 | 2018-02-23 | 2018-05-23
3 | 2 | 2018-02-23 | 2018-06-23
4 | 1 | 2018-04-23 | 2018-07-23
Customers table:
1 | Daniel | Sosa | Estrada | sosa@algo.com
2 | Juan | Casas | Sola | sola@algo.com
And I want to get the customer data, followed by their payment data but, with the most distant due date, expecting to get:
1 | Daniel | Sosa | Estrada | 2018-04-23 | 2018-07-23
2 | Juan | Casas | Sola | 2018-02-23 | 2018-06-23
I have a query, which is similar but does not give me the expected results:
SELECT
cliente.idcliente,
nombre,
apellidoPaterno,
apellidoMaterno,
fechaEnQuePaga,
fechavencimiento
FROM cliente, pagos
WHERE (cliente.idcliente = pagos.idcliente)
GROUP BY idcliente;
Result:
1 | Daniel | Sosa | Estrada | 2018-02-23 | 2018-03-25
2 | Juan | Casas | Sola | 2018-02-23 | 2018-05-23
I noticed that if I add MAX()
it locates the furthest due date, but it does not show its corresponding payment date:
SELECT
cliente.idcliente,
nombre,
apellidoPaterno,
apellidoMaterno,
fechaEnQuePaga,
MAX(fechavencimiento)
FROM cliente, pagos
WHERE (cliente.idcliente = pagos.idcliente)
GROUP BY idcliente;
Result:
1 | Daniel | Sosa | Estrada | 2018-02-23 | 2018-07-25
2 | Juan | Casas | Sola | 2018-02-23 | 2018-06-23
The payment date does not correspond to the one in the row.
Thank you for your answer.
Answer:
Window functions are great for this type of query, but MySQL doesn't have them 🙁
What you can do is a normal join (or a left join if a customer might not have payments), but add a correlative subquery to it that filters the results by MAX(fechavencimiento)
:
select c.idcliente,
c.nombre,
c.apellidoPaterno,
c.apellidoMaterno,
p.fechaEnQuePaga,
p.fechavencimiento
from cliente c
left join pagos p
on p.idcliente = c.idcliente
and p.fechavencimiento = (select max(p2.fechavencimiento)
from pagos p2
where p2.idcliente = p.idcliente)
Warning: If a customer can have duplicate fechavencimiento
values in the pagos
table, then there is a chance that this query will return more than one record per customer.