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
Clients table:
1 | Daniel | Sosa | Estrada | [email protected]
2 | Juan | Casas | Sola | [email protected]
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, 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.