Getting unique data from table using MySQL

Question

My goal is to get all the last messages that user A had with his other friends.

I have a table friend and message

friend table is as follows

+---------+-----------------+------+-----+---------+----------------+
| Field   | Type            | Null | Key | Default | Extra          |
+---------+-----------------+------+-----+---------+----------------+
| id      | int(6) unsigned | NO   | PRI | NULL    | auto_increment |
| user1   | int(6) unsigned | NO   | MUL | NULL    |                |
| user2   | int(6) unsigned | NO   | MUL | NULL    |                |
| pending | tinyint(1)      | NO   |     | NULL    |                |
+---------+-----------------+------+-----+---------+----------------+

message table is as follows

+-----------------+-----------------+------+-----+-------------------+-----------------------------+
| Field           | Type            | Null | Key | Default           | Extra                       |
+-----------------+-----------------+------+-----+-------------------+-----------------------------+
| id              | int(6) unsigned | NO   | PRI | NULL              | auto_increment              |
| sender          | int(6) unsigned | NO   | MUL | NULL              |                             |
| receiver        | int(6) unsigned | NO   | MUL | NULL              |                             |
| message         | varchar(255)    | NO   |     | NULL              |                             |
| message_read    | tinyint(1)      | NO   |     | NULL              |                             |
| message_visible | int(6)          | YES  |     | NULL              |                             |
| message_date    | timestamp       | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------------+-----------------+------+-----+-------------------+-----------------------------+

Here is profile table

+----------+-----------------+------+-----+---------+----------------+
| Field    | Type            | Null | Key | Default | Extra          |
+----------+-----------------+------+-----+---------+----------------+
| id       | int(6) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id  | int(6) unsigned | NO   | MUL | NULL    |                |
| nickname | varchar(50)     | YES  | UNI | NULL    |                |
| email    | varchar(50)     | YES  |     | NULL    |                |
| image    | mediumblob      | YES  |     | NULL    |                |
+----------+-----------------+------+-----+---------+----------------+

As requested, here is the data set of message

+----+--------+----------+-------------------------------------------------------------+--------------+-----------------+---------------------+
| id | sender | receiver | message                                                     | message_read | message_visible | message_date        |
+----+--------+----------+-------------------------------------------------------------+--------------+-----------------+---------------------+
|  1 |      1 |        2 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
|  2 |      1 |        2 | test                                                        |            1 |            NULL | 2017-09-30 21:10:16 |
|  3 |      2 |        1 | test                                                        |            1 |            NULL | 2017-09-29 21:10:15 |
|  4 |      2 |        1 | test                                                        |            1 |            NULL | 2017-09-30 21:10:14 |
|  5 |      1 |        4 | test                                                        |            1 |            NULL | 2017-09-30 21:10:14 |
|  6 |      1 |        4 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
|  7 |      4 |        1 | test                                                        |            1 |            NULL | 2017-09-29 21:10:15 |
|  8 |      4 |        1 | test                                                        |            1 |            NULL | 2017-09-30 21:10:17 |
|  9 |      5 |        1 | test                                                        |            1 |            NULL | 2017-09-30 21:10:18 |
| 10 |      5 |        1 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
| 11 |      1 |        5 | test                                                        |            1 |            NULL | 2017-09-30 21:10:14 |
| 12 |      1 |        5 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
| 13 |      1 |        7 | test                                                        |            1 |               7 | 2017-09-30 21:10:15 |
| 14 |      2 |        3 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
| 15 |      2 |        3 | test                                                        |            1 |            NULL | 2017-09-30 21:10:14 |
| 16 |      3 |        2 | test                                                        |            1 |            NULL | 2017-09-30 21:10:17 |
| 17 |      3 |        2 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
| 18 |      3 |        4 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
| 19 |      3 |        4 | test                                                        |            1 |            NULL | 2017-09-30 21:10:14 |
| 20 |      4 |        3 | test                                                        |            1 |            NULL | 2017-09-30 21:10:17 |
| 21 |      4 |        3 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
| 22 |      2 |        4 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
| 23 |      2 |        4 | test                                                        |            1 |            NULL | 2017-09-30 21:10:14 |
| 24 |      4 |        2 | test                                                        |            1 |            NULL | 2017-09-30 21:10:17 |
| 25 |      4 |        2 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
| 26 |     11 |        4 | test1                                                       |            1 |            NULL | 2017-10-19 13:24:53 |
| 27 |     11 |        6 | test2                                                       |            1 |            NULL | 2017-10-19 19:56:46 |
| 28 |     11 |        7 | test3                                                       |            1 |            NULL | 2017-10-19 12:30:38 |
| 29 |      4 |       11 | test1_response                                              |            1 |            NULL | 2017-10-19 21:03:27 |
| 30 |      6 |       11 | test2_response                                              |            1 |            NULL | 2017-10-19 21:03:34 |
| 31 |     13 |        6 | test1                                                       |            1 |            NULL | 2017-10-20 09:36:09 |
| 32 |     13 |        7 | test1                                                       |            1 |            NULL | 2017-10-20 09:36:13 |
| 33 |     11 |       13 | test11                                                      |            1 |            NULL | 2017-10-20 09:36:21 |
| 34 |     13 |       11 | test11_response                                             |            1 |            NULL | 2017-10-20 09:41:27 |
| 35 |   1013 |        2 | test                                                        |            1 |            NULL | 2018-03-29 13:35:31 |
| 36 |      2 |     1013 | Longer text message test, and I wish this works beautifully |            1 |            NULL | 2018-03-29 15:12:33 |
| 37 |   1013 |        2 | Longer text message test, and I wish this works beautifully |            1 |            NULL | 2018-03-29 15:17:40 |
| 38 |   1013 |        2 | Longer text message test, and I wish this works beautifully |            1 |            NULL | 2018-03-29 15:41:55 |
| 39 |   1015 |        2 | What's up                                                   |            1 |            NULL | 2018-04-17 16:33:29 |
| 40 |      2 |     1015 | What up?                                                    |            1 |            NULL | 2018-04-17 16:33:29 |
| 42 |      2 |     1015 | What's up                                                   |            0 |            NULL | 2018-04-17 16:33:29 |
+----+--------+----------+-------------------------------------------------------------+--------------+-----------------+---------------------+

Below is the data set of friend

+-----+-------+-------+---------+
| id  | user1 | user2 | pending |
+-----+-------+-------+---------+
|   1 |     1 |     2 |       0 |
|   3 |     1 |     5 |       0 |
|   4 |     6 |     1 |       0 |
|   6 |     2 |     5 |       0 |
|   7 |     3 |     5 |       0 |
|   8 |     6 |     2 |       0 |
|   9 |     6 |     1 |       0 |
|  10 |     5 |     4 |       0 |
|  11 |     4 |     1 |       0 |
|  12 |     1 |     7 |       1 |
|  19 |    12 |     1 |       1 |
|  20 |    12 |     2 |       0 |
|  22 |    12 |     4 |       0 |
|  23 |    12 |    11 |       0 |
|  25 |     6 |    11 |       0 |
|  26 |     7 |    11 |       0 |
|  27 |     7 |    12 |       0 |
|  28 |     6 |    13 |       0 |
|  29 |     7 |    13 |       0 |
|  30 |    11 |    13 |       0 |
|  31 |  1013 |     2 |       0 |
|  39 |  1015 |     2 |       0 |
| 104 |  1015 |  1021 |       1 |
+-----+-------+-------+---------+

Below is snippet of profile data

+------+---------+----------+-------+-------+
| id   | user_id | nickname | email | image |
+------+---------+----------+-------+-------+
|    1 |       1 | user1    |       | NULL  |
|    2 |       2 | user2    | NULL  | NULL  |
|    3 |       3 | user3    | NULL  | NULL  |
|    4 |       4 | user4    | NULL  | NULL  |
| 1014 |    1018 | user1019 | NULL  | NULL  |
| 1015 |    1019 | user1020 | NULL  | NULL  |
| 1016 |    1020 | user1021 | NULL  | NULL  |
| 1017 |    1021 | user1022 | NULL  | NULL  |
+------+---------+----------+-------+-------+

I coded out my statement referencing some great answers posted on StackOverFlow

Here is my sql statement:

SELECT msg.message, msg.message_read, msg.message_date,   
CASE WHEN msg.receiver = 1013  
THEN (SELECT nickname FROM profile WHERE user_id = msg.sender) 
ELSE (SELECT nickname FROM profile WHERE user_id = msg.receiver) 
END AS name, 

CASE WHEN msg.receiver = 1013 
THEN (SELECT image FROM profile WHERE user_id = msg.sender)  
ELSE (SELECT image FROM profile WHERE user_id = msg.receiver)  
END AS image  

FROM message msg  
JOIN  
(SELECT user, max(message_date) m  
FROM  
((SELECT id, receiver user, message_date FROM message WHERE sender = 1015 AND (message_visible =1 OR message_visible IS NULL))  
UNION  
(SELECT id, sender user, message_date FROM message WHERE receiver = 1015 AND (message_visible =1 OR message_visible IS NULL))) m1 GROUP BY user) m2 ON  
((sender = 1015 AND receiver = user) OR (sender = user AND receiver = 1015)) AND (message_date = m) ORDER BY message_date desc;

The result I get is

+-----------+--------------+---------------------+----------+-------+
| message   | message_read | message_date        | name     | image |
+-----------+--------------+---------------------+----------+-------+
| What's up |            1 | 2018-04-17 16:33:29 | user2    | NULL  |
| What up?  |            1 | 2018-04-17 16:33:29 | user1016 | NULL  |
| What's up |            0 | 2018-04-17 16:33:29 | user1016 | NULL  |
+-----------+--------------+---------------------+----------+-------+

I would like to get only one of these.. It does not really matter which one. Of course, there are going to be more messages if there are more users. The problem is when I have same message_date. Although it seems implausible to have message sent at the same time, I would at least want to learn how to control this situation.

Please add a data set. Ok our result but it's important too the data set — Joe Taras
– Joe Taras, Commented Apr 18, 2018 at 14:38
@JoeTaras I just added a data set. Profile just holds the name of each user, so I did not add this data set — John
– John, Commented Apr 18, 2018 at 14:43

Juan Carlos Oropeza · Accepted Answer · 2018-04-18 19:43:37Z

3

First your get the messages from your userA with anyone else. I didnt include the condition for when message arent visible because the logic wasnt clear from the question. You will have to add it yourself.

SQL DEMO (without profile table)

SELECT m.message, 
       m.message_read,
       m.message_date,
       CASE WHEN m.sender = @userA_ID
            THEN m.receiver
            ELSE m.sender
       END as friend_id,           
       CASE WHEN m.sender = @userA_ID
            THEN p2.nickname
            ELSE p1.nickname
       END as name,
       CASE WHEN m.sender = @userA_ID
            THEN p2.image
            ELSE p1.image
       END as image
FROM message as m
JOIN profile as p1
  ON m.sender = p1.user_id    -- sender
JOIN profile as p2 
  ON m.receiver = p1.user_id  -- receiver
WHERE @userA_ID IN (m.sender, m.receiver)

Now you use variables to order the message by friend and date, notice you use friend_id in case two friends have the same name.

SELECT t.*,
       @rn := if(@friend = t.friend_id, 
                 @rn + 1,
                 if( @friend := t.friend_id, 1, 1)
                ) as rn
FROM ( ** previous_query ** ) as t
CROSS JOIN ( SELECT @rn := 0, @friend := 0 ) as var
ORDER BY t.friend_id, t.message_date desc

notice you can add another condition to order to handle ties when same date

Finally you filter the message with rn = 1

SELECT *
FROM ( ** second query **) q
WHERE q.rn = 1

edited Apr 18, 2018 at 19:43

answered Apr 18, 2018 at 4:34

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

John Over a year ago

Hi, I am using Java to handle this data. I am not sure how to have multiple statements. Would it be possible to put all together in one statement? Message_visible is an indicator that shows who can look at the message. If it is null both parties can look at the message. Otherwise, only the indicated person can see the message. It just safeguards that the user who deleted the message cannot retrieve it again.

Juan Carlos Oropeza Over a year ago

Yes, Just put the previous query inside the parenthesis. I split it so is easy to understand. But first you should test it inside mysql to see if is working as desire

Juan Carlos Oropeza Over a year ago

check my edit. I add a demo using your sample data. Without profile

John Over a year ago

Holy moly! That works just beautifully. Is there any way to optimize this?

Juan Carlos Oropeza Over a year ago

please try again. Remember change m.sender = 1 for m.sender = @yourSearchID

|

Collectives™ on Stack Overflow

Getting unique data from table using MySQL

1 Answer 1

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related