CODEWITHSUNDEEP
mysqlsql
Anam
Anam

Reputation: 12189

group messages - MySQL

I have a table called messages that stores user messages, and the table structure looks like this:

Messages:

id   from_id    to_id  content
1    1          2       ABC
2    2          1       BCC
3    1          2       EFG
1    4          2       GHJ
2    2          4       MNX
3    15         2       LKH

Is it possible to run a query to group messages like the following?

Expected Output:

   from_id  to_id    
    1       2               
    4       2           
    15      2

Conversation between two parties will be in one group. So, we can see from table messages, there are 3 groups.

Upvotes: 0

Views: 280

Answers (3)

peterm
peterm

Reputation: 92805

You can do either

SELECT DISTINCT LEAST(from_id, to_id) from_id, 
                GREATEST(from_id, to_id) to_id
  FROM messages;

Output:

| FROM_ID | TO_ID |
|---------|-------|
|       1 |     2 |
|       2 |     4 |
|       2 |    15 |

or

SELECT from_id, to_id
  FROM messages
 GROUP BY LEAST(from_id, to_id), 
          GREATEST(from_id, to_id);

Output:

| FROM_ID | TO_ID |
|---------|-------|
|       1 |     2 |
|       4 |     2 |
|      15 |     2 |

Here is SQLFiddle demo

Upvotes: 2

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

I've come with the following solution. We LEFT join the messages with responses. Then, we select distinct pairs of from_id and to_id, but we use CASE expression to decide if we'll take the from_id and to_id from the first message, or a response. This is based on an assumption that it is ok to always have a smaller id as from_id and larger id in to_id.

Solution:

CREATE TABLE messages (
    id INT,
    from_id INT,
    to_id INT,
    content VARCHAR(20)
);


INSERT INTO messages VALUES (1, 1, 2, 'ABC');
INSERT INTO messages VALUES (2, 2, 1, 'BCC');
INSERT INTO messages VALUES (3, 1, 2, 'EFG');
INSERT INTO messages VALUES (1, 4, 2, 'GHJ');
INSERT INTO messages VALUES (2, 2, 4, 'MNX');
INSERT INTO messages VALUES (3, 15, 2, 'LKH');

SELECT
    DISTINCT
      CASE
        WHEN m2.from_id IS NULL
          OR m1.from_id < m2.from_id THEN m1.from_id
        ELSE m2.from_id
      END AS from_id,
      CASE
        WHEN m2.to_id IS NULL
          OR m1.to_id > m2.to_id THEN m1.to_id
        ELSE m2.to_id
      END AS to_id
  FROM
    messages m1
    LEFT JOIN messages m2 ON (m1.from_id = m2.to_id AND m1.to_id = m2.from_id)
;

FROM_ID    TO_ID
1          2
2          4
15         2

Check at SQLFiddle

Upvotes: 0

T I
T I

Reputation: 9943

I think you're looking for DISTINCT

SELECT DISTINCT from_id, to_id
FROM messages
WHERE to_id=2

Upvotes: 0

Related Questions