two different users join
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