Xavier
Xavier

Reputation: 131

SQL Request to select conversations by userID

I have 4 tables :

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

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

Answers (1)

Raffaello.D.Huke
Raffaello.D.Huke

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

Related Questions