SQL Request to select conversations by userID

Question

I have 4 tables :

1. conversations
2. conversations_users
3. messages
4. users

The conversations structure table is :

• id (primary key)
• title (conversation title)

The conversations_users structure table is :

• id (primary key)
• conversationID (the conversation id where the user is in)
• userID (the user associated to a conversation id)

The messages structure table is :

• id (primary key)
• message (the message text)
• conversationID (the conversation id where the message is in)
• senderID (the user id of who sent the message)
• date (timestamp of the message)

The users structure table is :

• id (primary key)
• username

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

Answer 1

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