SQL 连接查询未按预期运行

Question

我有下表:

CREATE TABLE `attendance_event_attendance` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`talk_id` varchar(200) NOT NULL,
`membersAttended_id` varchar(200) NOT NULL,
PRIMARY KEY (`id`),
KEY `attendance_event_attendance_9ace4e5a` (`talk_id`),
KEY `attendance_event_attendance_3c0dadb7` (`membersAttended_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

CREATE TABLE `attendance_member` (
`name` varchar(200) NOT NULL,
`telephone_number` varchar(200) NOT NULL,
`email_address` varchar(200) NOT NULL,
`membership_type` varchar(1) NOT NULL,
`membership_number` varchar(200) NOT NULL,
PRIMARY KEY (`membership_number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `attendance_talk` (
`title` varchar(200) NOT NULL,
`speaker` varchar(200) NOT NULL,
`date_of_talk` date NOT NULL,
PRIMARY KEY (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

我想选出所有没有参加最近两次演讲的成员。我编写的查询如下所示:

SELECT m.name 
  from attendance_member as m 
left outer join attendance_event_attendance as ea on (ea.membersAttended_id=m.membership_number) 
join attendance_talk as t on (ea.talk_id = t.title) 
where t.date_of_talk >= 2010-06-01 
  AND ea.membersAttended_id = null;

这是正确的吗？或者我没有正确理解连接？

Answer 1

我担心这是一种有点可怕的方法 - 但应该有效......

SELECT m.name 
from attendance_member as m 
left outer join (
 SELECT ea.membersAttended_id
 FROM attendance_event_attendance as ea
 join attendance_talk as t on (ea.talk_id = t.title) 
 where t.date_of_talk >= 2010-06-01
 GROUP BY ea.membersAttended_id
 HAVING COUNT(*) = 2
) attendingmembers
ON attendingmembers.membersAttended_id = m.membership_number
WHERE attendingmembers.membersAttended_id IS NULL