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.
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)
:Warning: If a customer can have duplicate values
fechavencimiento
in the tablepagos
, then there is a chance that this query will return more than one record per customer.You could do the following:
Let's see:
P1
we obtain the maximumfechaVencimiento
per clientP2
we obtain the record(s) whose payments correspond to the maximumfechaVencimiento
ofP1
. Important , if you had several records for a customer with the samefechaVencimiento
(in your example it seems that it does not happen) you should add one more nesting to "decide" which row you keep, as long as what you expect is to have a single row per customer.JOIN
end betweenclientes
andpagos
, use aLEFT
if you don't want to lose sight of those customers who don't have payments or aINNER
if you want to eliminate them from the query.This is a pretty basic solution using plain SQL, I imagine in MySql there are better ways to solve it.
Use INNER JOIN with ORDER BY
In this way you will be able to obtain the union of your rows and you will be able to filter by the field that you want in the order that you specify (ASC (ascending), DESC (descending))
The query that resembles it is because when it groups, it takes the last value of the group, so you would only have to add a GROUP BY,
If this doesn't work for you, change ASC to DESC and try again.
You have to be careful with this, if you import it to a mysql that has the constraint ONLY_FULL_GROUP_BY it will show an error
as they mention this would be with inner join like this