Database conversations get list of sent messages

Question

Following up on the following post: Get last row PER Group

Let's say you are logged in as user:1 I want to get one row per sent messages that have not yet received a response in the conversation group (essentially the conversation would only have 1 message). For each conversations I want to get, the "Subject" of the conversation, ToUser and DateTime

Any help would be greatly welcome, the last question on this subject was invaluable to me and I really much appreciate it.

    CREATE TABLE messages (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    FromID INT NOT NULL,   
    ToID INT NOT NULL,    
    ConversationID INT NOT NULL, 
    Message varchar(255),
    DateTime DATETIME                                       
    ) ENGINE=InnoDB;


CREATE TABLE conversations (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Subject varchar(255)            
    ) ENGINE=InnoDB;



INSERT INTO conversations (ID, Subject) VALUES (1, "hey"), (2, "hey2"), (3, "hey3");
INSERT INTO messages (FromID, ToID, ConversationID, Message, DateTime) VALUES (1,2, 1, "Hi", "This is a test message", "2010-08-08 16:23:48");        
INSERT INTO messages (FromID, ToID, ConversationID, Message, DateTime) VALUES (1,2, 1, "", "Hey again you have not answered", "2010-08-08 16:23:52");                                                                                                                                               
INSERT INTO messages (FromID, ToID, ConversationID, Message, DateTime) VALUES (2,1, 1, "", "Hi this is my answer", "2010-08-08 16:23:59");


INSERT INTO messages (FromID, ToID, ConversationID, Message, DateTime) VALUES (2,1, 2, "2.Hi", "2.This is a test message", "2010-08-08 16:25:48");        
INSERT INTO messages (FromID, ToID, ConversationID, Message, DateTime) VALUES (1,2, 2, "", "2.Hi back", "2010-08-08 16:25:52");                                                                                                                                               
INSERT INTO messages (FromID, ToID, ConversationID, Message, DateTime) VALUES (2,1, 2, "", "2.Hi this is my answer", "2010-08-08 16:25:59");


INSERT INTO messages (FromID, ToID, ConversationID, Message, DateTime) VALUES (2,1, 3, "3.Hi", "3.This is a test message", "2010-08-08 16:27:48");        
INSERT INTO messages (FromID, ToID, ConversationID, Message, DateTime) VALUES (1,2, 3, "", "2.Hi back", "2010-08-08 16:27:52");   
INSERT INTO messages (FromID, ToID, ConversationID, Message, DateTime) VALUES (1,2, 3, "", "2.Hello are you there?", "2010-08-08 16:27:59");

score 1 · Accepted Answer · answered Dec 23 '10 at 04:13

1

If I understand what you're looking for, I believe this query will get you what you want:

SELECT `Subject`, `ToID`, `DateTime`, COUNT(*) AS `NumberOfMessagesInConversation`
FROM `messages`
GROUP BY `ConversationID`
HAVING `NumberOfMessagesInConversation` = 1

Edit: this query should get you the same thing, but will probably be more efficient when you have a ton of conversations:

SELECT `Subject`, `ToID`, `DateTime`
FROM
(
    SELECT `ConversationID`, COUNT(*) AS `NumberOfMessagesInConversation`
    FROM `messages`
    GROUP BY `ConversationID`
    HAVING `NumberOfMessagesInConversation` = 1
) AS `message_counts`
JOIN `messages` ON `messages`.`ConversationID` = `message_counts`.`ConversationID`

answered Dec 23 '10 at 04:13

TehShrike

9,855
2
33
28

hey thanks alot! sorry one mistake I had made I actually have the subject in the other table since its conversation style there is always only one subject. How could you modify your query to retrieve t? – user391986 Dec 23 '10 at 04:33
Add another JOIN like so: `JOIN conversations ON conversations.ID = messages.ConversationID` and then add `conversations.subject` to the SELECT part of your query. Don't forget to accept this answer if it's correct :-) – TehShrike Dec 23 '10 at 05:02

Database conversations get list of sent messages

1 Answers1