SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2005
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Grouping columns into conversations

    Hi,

    I've been struggling to update an old messaging script to a conversational view.

    What I have at the moment is a PMs database that looks a bit like this:

    message_id subject from_uid to_uid time
    1 Hey mate 300 100 1332694635
    2 Hey mate 100 300 1332694835
    8 Hey mate 300 100 1332694935
    14 Hey mate 100 300 1332695835
    ...etc

    What I have been trying to do is combine to and from messages, and set a new column "conversation id".

    This would group related messages under 1 id (the message_id of the earliest message) and show as a group of both to and from.

    Tried grouping by to_userid and using it to set a conversation_id. It worked, but I couldn't also group the reverse (e.g. replies from from_id to to_id).

    Would anyone have any idea on how to go about doing this?

    Many thanks,
    Jbcubed3

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jbcubed3 View Post
    What I have been trying to do is combine to and from messages, and set a new column "conversation id".
    mind showing what the result of this combining would produce for the sample data you gave?

    Quote Originally Posted by jbcubed3 View Post
    Tried grouping by to_userid and using it to set a conversation_id. It worked, but I couldn't also group the reverse (e.g. replies from from_id to to_id).
    don't forget that GROUP BY is an aggregation operation which destroys individual detail rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

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
  •