MySQL Forums
Forum List  »  Newbie

two different users join
Posted by: Jassim Rahma
Date: July 23, 2021 11:10AM

Hi,

I have a chat table with the following:

CREATE TABLE `conversations` (
`conversation_id` bigint(20) NOT NULL AUTO_INCREMENT,
`conversation_uuid` varchar(255) DEFAULT NULL,
`post_id` bigint(20) DEFAULT NULL,
`created_date` datetime DEFAULT CURRENT_TIMESTAMP,
`created_user` bigint(20) DEFAULT NULL,
`created_ip` varchar(255) DEFAULT NULL,
PRIMARY KEY (`conversation_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1


This is the main table where the conversations are stored.

Then I have the chat history which has everything they text.

CREATE TABLE `messages` (
`message_id` bigint(20) NOT NULL AUTO_INCREMENT,
`message_uuid` varchar(255) DEFAULT NULL,
`conversation_id` bigint(20) DEFAULT NULL,
`message_text` text,
`created_date` datetime DEFAULT CURRENT_TIMESTAMP,
`created_user` bigint(20) DEFAULT NULL,
`created_ip` varchar(255) DEFAULT NULL,
PRIMARY KEY (`message_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1

both tables are linked by conversation_id

Now I want to list all messages but I want to know which message belongs to the originator and which message belongs to the replier.

The originator of the message is the same as the created_user inside the conversation

so what I am looking for is something like this:

SELECT msg.message_id, msg.message_text, msg.created_date,
originator.first_name_en AS first_name, originator.last_name_en AS last_name
FROM messages msg
JOIN conversations ON conversations.conversation_id = msg.conversation_id
JOIN oneid.users originator ON originator.user_id = conversations.created_user
WHERE msg.conversation_id = 1;


but the problem is what about the message belongs to the replier? How can I show it?

The logic for the replier is like this:

messages.created-user WHERE NOT the same as conversations.created_user

How can I achieve this please?


Thanks,
Jassim

Options: ReplyQuote


Subject
Written By
Posted
two different users join
July 23, 2021 11:10AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.