CODEWITHSUNDEEP
mysqlsql
jason
jason

Reputation: 3962

Select the Latest Message Between the communication of two users and group them

User no=1 which is Aa :

CREATE TABLE if not exists tblA
(
id int(11) NOT NULL auto_increment ,
sender varchar(255),
receiver varchar(255),
 msg varchar(255),
 date timestamp,
 PRIMARY KEY (id)
);

CREATE TABLE if not exists tblB
(
id int(11) NOT NULL auto_increment ,
sno varchar(255),
name varchar(255),
 PRIMARY KEY (id)
);


INSERT INTO tblA (sender, receiver,msg,date ) VALUES
('1', '2', 'buzz ...','2011-08-21 14:11:09'),
('1', '2', 'test ...','2011-08-21 14:12:19'),
('1', '2', 'check ...','2011-08-21 14:13:29'),
('1', '1', 'test2 ...','2011-08-21 14:14:09'),
('2', '1', 'check2 ...','2011-08-21 14:15:09'),
('2', '1', 'test3 ...','2011-08-21 14:16:09'),
('1', '2', 'buzz ...','2011-08-21 14:17:09'),
('1', '2', 'test ...','2011-08-21 14:18:19'),
('1', '2', 'check ...','2011-08-21 15:19:29'),
('1', '1', 'test2 ...','2011-08-21 14:10:09'),
('3', '1', 'check2 ...','2011-08-21 14:21:09'),
('3', '1', 'test3 ...','2011-08-21 14:22:09'),
('3', '2', 'buzz ...','2011-08-21 14:24:09'),
('3', '2', 'test ...','2011-08-21 14:25:19'),
('1', '3', 'check ...','2011-08-21 14:26:29'),
('1', '3', 'test2 ...','2011-08-21 14:27:09'),
('2', '3', 'check2 ...','2011-08-21 14:28:09'),
('2', '3', 'test3 ...','2011-08-21 14:29:09'),
('1', '2', 'check3 ...','2011-08-21 14:23:09'),
('1', '4', 'test2 ...','2011-08-21 14:27:09'),
('1', '5', 'test2 ...','2011-08-21 14:27:09'),
('2', '6', 'check2 ...','2011-08-21 14:28:09'),
('1', '7', 'test3 ...','2011-08-21 14:29:09'),
('8', '2', 'check3 ...','2011-08-21 14:23:09');


INSERT INTO tblB (sno, name ) VALUES
('1', 'Aa'),
('2', 'Bb'),
('3', 'Cc'),
('4', 'Dd'),
('5', 'Ee'),
('6', 'Ff'),
('7', 'Gg'),
('8', 'Hh');

How to get latest communication time b/n 2 users . http://www.sqlfiddle.com/#!2/ed676/1 the query is not giving latest times of communication.

I really appreciate any help.Thanks in Advance.

Upvotes: 0

Views: 241

Answers (4)

shruti
shruti

Reputation: 717

Try This :

select p1.name,p2.name,max(date) from
(select least(sender,receiver) min_val,greatest(sender,receiver) max_val,msg,date from tblA
order by min_val,max_val,date) t inner join tblB p1 on t.min_val=p1.sno
inner join tblB p2 on t.max_val=p2.sno
group by min_val,max_val;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Is this what you want?

select b.name, max(date)
from tblA a join
     tblB b
     on b.sno in (a.receiver, a.sender)
where b.sno <> '1' and 
      exists (select 1
              from tblB b2
              where b2.sno = '1' and
                    b2.sno in (a.receiver, a.sender)
             ) or
      (a.sender = 1 and a.receiver = 1)
group by b.name
order by max(date) desc;

This returns the latest time of communication for each "other person" talking to 1. Your original query didn't put max(date) in the select list.

Upvotes: 1

jeremycole
jeremycole

Reputation: 2761

Based on the discussion on my previous answer, I think what you want is something like this:

SELECT
  tblA.id,
  sender.name AS sender_name,
  receiver.name AS receiver_name,
  tblA.msg,
  tblA.date
FROM (
  SELECT
    LEAST(sender, receiver) AS a,
    GREATEST(sender, receiver) AS b,
    MAX(id) AS id
  FROM tblA
  GROUP BY a, b
) AS t
JOIN tblA ON t.id = tblA.id
JOIN tblB AS sender ON tblA.sender = sender.sno
JOIN tblB AS receiver ON tblA.receiver = receiver.sno

So the basic idea is:

  1. Choose the latest message id, auto_increment or some other form of continuously incrementing numeric PRIMARY KEY is assumed for the messages (tblA) table. This finds the IDs of those messages without regard to sender or receiver ordering.
  2. Based on the message id for each message, select its full detail.
  3. Join the message rows against the user (tblB) records twice, once to get sender details, and once for receiver details.

Upvotes: 0

jeremycole
jeremycole

Reputation: 2761

Your best bet is probably to use a UNION such as:

select * from (
  select * from tblA
  where sender = '1' and receiver = '2'
  union
  select * from tblA
  where sender = '2' and receiver = '1'
) as t
order by date desc
limit 5

You can do a few things to make this more efficient such as pushing down some of the limits and ordering into each part of the UNION, but this is the basic idea.

Upvotes: 0

Related Questions