CODEWITHSUNDEEP
sqlphpmyadmin
Viktors
Viktors

Reputation: 935

Get private message group by user MySQL/PHP

I try to make an SQL which bring me last message from each user who send me. I am using phpmyadmin.

I have two tables

users

id | username
6         6666
7         8888

messages

message_id | sender_id | receiver_id | subject | text | date
1            6           3             aaa       bbb    2012-07-22 00:14:41
2            6           3             aaa1      bbb1   2012-07-22 00:15:41
3            7           3             vvv       vvv    2012-07-22 00:19:41

so result must be (ordered by date(time))

3            7           3             vvv       vvv    2012-07-22 00:19:41
2            6           3             aaa1      bbb1   2012-07-22 00:15:41

I have made this query (I tested that my id = 3) below query working as I want but I think it is not so good (optimized) and there is more easier way to select this data

SELECT u.username, m.sender_id , m.subject, m.message
                FROM users u
                LEFT JOIN messages m ON m.sender_id = u.id OR m.message_id = (SELECT b.message_id FROM messages b WHERE b.receiver_id = 3 ORDER BY b.date DESC LIMIT 0,1)
                WHERE m.receiver_id = 3
                GROUP BY m.sender_id
                ORDER BY m.date DESC

Upvotes: 0

Views: 821

Answers (1)

hkutluay
hkutluay

Reputation: 6944

select * from messages 
where message_id in(select max(msg.message_id) from messages msg
                    where msg.receiver_id = 3 group by sender_id )

Upvotes: 1

Related Questions