mysql - 获取 user=1 存在的所有其他用户名

Question

我有以下“tblA”和“tblB”表:

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');

如何获取用户为:1的所有行，以及发送者/接收者的名称不是1的名称(即:Aa )

Answer 1

我认为这将获得唯一的列表:

select distinct b.name
from tblA a join
     tblB b
     on b.id in (a.sender, a.receiver) and
        ((b.id <> 1 and (a.sender = 1 or a.receiver = 1)) or
         (a.sender = 1 and a.receiver = 1)
        );