Grouping by two columns in a table

Discussion in 'Databases' started by Norrad, Apr 16, 2018.

0
  1. #1
    I'm trying to create a very simple messaging system for a website and I am trying to group conversations by from or to using a single SQL statement. I have simplified the statement I am trying to use below:

    $messages_query = "SELECT * FROM tbl_messages WHERE from_id='$current_user' OR to_id='$current_user' GROUP BY from_id, to_id ORDER BY message_date DESC";

    But ofcourse the grouping is a bit off. How would I get it to group by sender/receiver?
     
    Norrad, Apr 16, 2018 IP
  2. Cameron Fillers

    Cameron Fillers Member

    Messages:
    33
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    33
    #2
    Can you please provide the describe of both of the database tables? It is hard to ascertain the issue without being able to see the all of the fields for both tables.

    As opposed to the WHERE field, you may be better off utilizing the "HAVING condition" option.
     
    Cameron Fillers, Jun 18, 2018 IP
  3. AlBorne

    AlBorne Greenhorn

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #3
    yea, and what output are you looking for? GROUP BY is typically used with aggregate functions like SUM, AVG, MIN etc. Not sure what you are intending to do with it here.
     
    AlBorne, Mar 18, 2019 IP
  4. JEET

    JEET Notable Member

    Messages:
    3,832
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #4
    I sort of understand what you are trying to achieve, but group will not do that, if I am understanding you correctly.

    Suppose if "current_user" is a participant in 5 messages, then you wish to show him those 5 messages, and you wish to separate the messages based on the "other" reciever/sender, right?
    Group will not help here.

    You need to show the table structure...
     
    JEET, Mar 29, 2019 IP