I have the following two tables
User (Email(PK), Nombre, Rol) and Message (Id (Pk), IDSend (FK), IDDest(FK), Mensaje, Status, Fecha)
I need to return the Email, the Name, the message and the Status of the last message of each user registered in the database. Any ideas that you can give me?
This is what I have
Select IDSend, MAX(Fecha) as Date, Status, Mensaje
FROM message group by IDSend HAVING IDSend in (Select Email from user)
To do this in standard SQL, we would do the following:
We retrieve the last message of each user
Select IDSend, Max(Fecha)
From Message
Group by IDSend
Now, we use that query as input of another query, to be able to obtain the values of each of the other tables
Select U.Email, U.Nombre, M.Mensaje, M.Status
From (
Select IDSend, Max(Fecha)
From Message
Group by IDSend) as A
inner join User as U on U.Email = A.IDSend
inner join Message as M on M.Fecha = A.Fecha
What we do in this last query, is once we have obtained the IDSend we wanted, we cross it again with the tables that have the fields that we wanted.
Since table A has only one record per user and is the one with the maximum date, it will only bring that data.
There is another way to do this, and it is doing subquerys directly in the select. The result ends up being the same, but the syntax looks more complex.
One option may be the following
SELECT User.Email, User.Nombre,
Message.Mensaje, Message.Status
FROM User
JOIN Message ON User.Email = Message.IDSend
GROUP BY User.Nombre
ORDER BY('Message.Id', 'DESC')
What I did in the previous example was first list all the columns that I want to recover, later I do a
JOINwith the tableMessageafter that I do aGROUP BYso that if it finds coincidences plus a message from the same user put them all togetherÙser.Nombrefinally as I want to get the most recent of each user, make aORDER BY()using the Id column of the Message table, sinceIdis PK and incremental auto so I would be recovering the last record created