John
Reputation: 147
Getting unique data from table using MySQL
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.
Upvotes: 1
Views: 80
Answers (1)
Juan Carlos Oropeza
Reputation: 48197
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
Upvotes: 3
Related Questions
- Mysql get unique row
- i want to retrieve unique values from my table
- MySQL unique rows
- How to get unique data from a table?
- Getting unique record from a table in mysql
- mysql select unique records
- How to get unique result in mysql
- Mysql query for getting unique rows
- mysql query to get unique value from one column
- Trying to get unique records with MySQL