Results 1 to 2 of 2
May 9, 2012, 17:47 #1
Complex MySQL Distinct or Group By query
What I am trying to achieve is somewhat complex to do with one query so I'll begin by explaining the scenario:
Say for example two users talk via direct message, you will have a conversation of messages from both users to one another .. I want to just select the latest message from that conversation and then show it as the conversation link in their message inbox ... they can then click that last sent message to view the whole conversation (the second part is simple, just the first query I can't get fully working).
Facebook and Twitter message inboxes work just like this.
My messages table is in the following format:
targetUserId = the userId of the user the message is going to
sourceUserId = the userId of the user who sent the message
What I need to do to get the feature working is select all messages that the currently logged in user (1 in this example) has either sent or received and then group them so they are distinct, I can do this with the following:
SELECT id, sourceUserId, targetUserId, body, `time` FROM usermessages WHERE targetuserId = 1 OR sourceUserId = 1 GROUP BY targetUserId, sourceUserId ORDER BY id DESC
How can I modify the query to avoid these duplicate opposites?
May 10, 2012, 21:14 #2
I think! I have solved this with the following query:
SELECT id, sourceUserId, targetUserId, body `time` FROM usermessages WHERE targetuserId = 1 OR sourceUserId = 1 GROUP BY CASE WHEN targetUserId != 1 THEN targetUserId ELSE sourceUserId END ORDER BY id DESC