CODEWITHSUNDEEP
mysql
nonkertompf
nonkertompf

Reputation: 397

MySQL Select The Most Recent Incoming or Outgoing Messages of Distinct Users

I have a table structure where I keep records of the messages sent and received like below;

Database table structure

member_id1 is the sender and member_id2 is the receiver.

Now what I'm trying to achieve is to get the rows with the most recent messages of the user who has the member_id of 3. The result should contain only 2 rows with the message ids of 2 and 4, as 2 is the last message between the user with the member_id of 3 and the user with the member_id of 2, and 4 is the only message between the user with the member_id of 3 and the user with the member_id of 1 yet.

The result shouldn't contain the rows with the message_id of 3 for it has nothing to do with the user who has the member_id of 3, and the message_id of 1 because it has been overdated by the message with the message_id of 2.

Hope I've made my question clear for people who can help me with this problem.

Upvotes: 0

Views: 244

Answers (3)

nonkertompf
nonkertompf

Reputation: 397

I think I've figured out the right answer for my own question. Although I'm not quite sure, it seems to work great. For those who need a solution to such a problem; give this a try:

SELECT m1.* 
  FROM table_name m1 
  INNER JOIN (SELECT MAX(senddate) AS senddate, 
                     IF(member_id2 = 3, member_id1, member_id2 ) AS user 
                FROM table_name 
               WHERE (member_id1 = 3 AND delete1=0) OR 
                     (member_id2 = 3 AND delete2=0) 
              GROUP BY user) m2 
         ON m1.senddate = m2.senddate AND 
           (m1.member_id1 = m2.user OR m1.member_id2 = m2.user) 
     WHERE (member_id1 = 3 AND delete1=0) OR 
           (member_id2 = 3 AND delete2=0) 
   ORDER BY m1.senddate DESC

Upvotes: 1

Patrick64
Patrick64

Reputation: 440

You could use UNION to combine them

 (SELECT * FROM table_name WHERE member_id2 = 3  ORDER BY senddate DESC limit 1)
union 
 (SELECT * FROM table_name WHERE member_id1 = 3  ORDER BY senddate DESC limit 1)

or if you need to get the latest sent/received for every member you could inner join a query that gets the max senddate for each:

select * from table_name t1 inner join 
(
 select member_id1, null as member_id2, max(senddate) as senddate 
 from table_name group by member_id1
 UNION
 select null as member_id1, member_id2, max(senddate) as senddate 
 from table_name group by member_id2
) r ON (t1.member_id1 = r.member_id1 OR t1.member_id2 = r.member_id2) 
AND t1.senddate = r.senddate

Upvotes: 0

Pablo Jomer
Pablo Jomer

Reputation: 10378

The question is a bit fuzzy but from what I understand your looking for this?

 SELECT * FROM table_name WHERE member_id2 = 3 ORDER BY senddate DESC;

Upvotes: 0

Related Questions