CODEWITHSUNDEEP
mysqlsql
jason
jason

Reputation: 3962

group by different user names other than the receiver name

I have the following "tblA" and "tblB" table:

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 14: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'),
('2', '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');

I want the list of people Aa is talking to ordered by Date (Last entry for each user group).

Bb
Cc
Dd
Ee
Ff
Gg
Hh

I'm getting stuck on how to Group them without including "Aa" in one query.

Upvotes: 1

Views: 54

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

If I understand correctly:

select b.name
from tblA a join
     tblB b
     on b.id in (a.receiver, a.sender)
where b.name <> 'Aa'
order by date desc;

Here is a SQL Fiddle. Note that your above code has a few semicolons that should be commas.

EDIT:

If you want the most recent date, then use:

select b.name
from tblA a join
     tblB b
     on b.id in (a.receiver, a.sender)
where b.name <> 'Aa'
group by b.name
order by max(date) desc;

EDIT II:

To filter by rows that just have 'Aa':

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

Upvotes: 2

Related Questions