SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member danthorpe's Avatar
    Join Date
    Oct 2008
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:

    Attachment 59796

    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:

    Code:
    SELECT id, sourceUserId, targetUserId, body, `time`
    FROM usermessages
    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:

    issue.png

    How can I modify the query to avoid these duplicate opposites?

  2. #2
    SitePoint Member danthorpe's Avatar
    Join Date
    Oct 2008
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think! I have solved this with the following query:

    Code:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •