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`
WHERE targetuserId = 1 OR sourceUserId = 1
GROUP BY targetUserId, sourceUserId
ORDER BY id DESC
BUT I still get opposite rows as highlighted in this image in the two red rows, notice the targetUserId and sourceUserId are the same but the other way around:
How can I modify the query to avoid these duplicate opposites?