Join two tables and sort by latest timestamp desc

Databases
1

Hi

I have two tables - one where an administrator talks to a user in a chat session and vice versa. I would have liked to put the chats in one table - but the login characteristics of the admin are different then the non login of casual chat users - so I just did two tables.

I wish to display the chat history between the two types of users using the Id of each as they are logged in on their respective session.

How can I join by the two types and select their chat history where to user in one table = equals from user in the other … and then sort by the most recent timestamp in Either table to get a chronological display of their chat?

I tried inner joins and left joins but couldn’t seem to g et it to work

Something like select * from admin_chat_message and select * from user chat message where a.to_user = u.from user or u.to_user = a.from_usee by sort by timestamp
… where timestamp is the most recent in either table.

Pls see sample tables below:

(Really the objective is to just the chat history of both user types sorted in reverse chronological order)

TABLES

admin chat message (

id int not bull auto_increment

to_user_id int not null

from_user_id int not null

chat-message text not null,

timestamp timestamp not null autonincremnt default current_timestamp(),

status int(1) not null,

Primary Key (id)

):

user_chat_message (

id int not null auto_increment

to_user_id int not null

from_user_id int not null

chat-message text not null,

timestamp timestamp not null autonincremnt default current_timestamp(),

status int(1) not null,

Primary Key (id)

):

2

Well you’re close here. You will do exactly one SELECT’ing, FROM one table JOIN’d with the other ON a.to_user = u.from_user OR a.from_user = u.to_user, ORDERed BY oneofthetables.timestamp DESCending (assuming you want the newest first.)

3

Hi what if the other table is the latest? Like they are chatting back and forth? How will I know which table has the most recent timestamp?

4

This is why it’s best to have it all in one table. I still don’t see a good reason not to have it all in one table.

Hang on. Do you create an entry in both tables when a user sends a message to an admin?

5 
SELECT user_from.from_user_id
     , user_to.to_user_id
     , user_from.timestamp
     , user_from.chat_message
  FROM admin_chat_message AS user_from
INNER
  JOIN user_chat_message AS user_to
    ON user_to.to_user_id = user_from.from_user_id
UNION ALL
SELECT user_from.from_user_id
     , user_to.to_user_id
     , user_from.timestamp
     , user_from.chat_message
  FROM user_chat_message AS user_from
INNER
  JOIN admin_chat_message AS user_to
    ON user_to.to_user_id = user_from.from_user_id
ORDER
    BY timestamp DESC
6

Thanks r937! I will use that query and also explore putting it all in one one table - as the conversations are always admin - user and vice versa , I may be able to do it (ie no null values…). So great advice and queries from everyone … as always!

7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.