Reputation: 131
I have 4 tables :
The conversations structure table is :
The conversations_users structure table is :
The messages structure table is :
The users structure table is :
I want to display all the conversations (title, last message, and last message timestamp) for an user so only the conversations where the user is in.
How can I do with a SQL request ? I guess it will be like this :
SELECT c.title, m.message, m.date
FROM conversation c
INNER JOIN conversation_users cu
ON ...
INNER JOIN messages m
ON ...
INNER JOIN users u
ON ...
GROUP BY u.id
Upvotes: 0
Views: 145
Reputation: 552
select usr.userID,
cvs.title,
msg.message,
times.date
from conversations_users usr
left join conversations cvs on usr.conversationID = cvs.id
left join messages msg on usr.conversationID = msg.conversationID
left join
(select usr.userID,
MAX(msg.date) as date
from conversations_users usr
left join messages msg on usr.conversationID = msg.conversationID
group by usr.userID) times on times.userID= usr.userID and times.date=msg.date
Upvotes: 1