MySQL - 有多少人不在连接表中？

Question

我正在运行以下查询，它告诉我在位置 51 的项目上曾经安排过哪些代理:

SELECT agents.agentid, agents.firstname, agents.lastname,
       schedule.projectid, projects.locationid
FROM agents
LEFT JOIN schedule USING (agentid)
LEFT JOIN projects USING (projectid)
WHERE (projects.locationid <=> 51)
GROUP BY agentid
ORDER BY agentid;

它产生 1249 行(这是正确的结果集)。

如果我想查看相反的情况，即从未在位置 51 的项目上安排过的代理，我会将 WHERE 子句更改为:

WHERE NOT (projects.locationid <=> 51)

它产生 16169 行。 1249+16169 = 比代理表中的行数还要多。

这显然是因为一名代理可以在多个地点参与一个项目，因此当发生这种情况时，他会同时出现在两个地点。

所以我的问题是，如何使第二个查询工作？也就是说，如何找出哪些代理从未参与过位置 51 的项目？

感谢您的帮助!

编辑:以下是表结构和示例数据:

CREATE TABLE IF NOT EXISTS `agents` (
`agentid` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`firstname` VARCHAR(45) NOT NULL,
`lastname` VARCHAR(45) NOT NULL,
PRIMARY KEY (`agentid`));

CREATE TABLE IF NOT EXISTS `schedule` (
`scheduleid` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`agentid` SMALLINT(5) UNSIGNED NOT NULL,
`projectid` SMALLINT(5) UNSIGNED NOT NULL,
PRIMARY KEY (`scheduleid`));

CREATE TABLE IF NOT EXISTS `projects` (
`projectid` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`locationid` SMALLINT(5) UNSIGNED NOT NULL,
PRIMARY KEY (`projectid`));

INSERT INTO `agents` (`agentid`,`firstname`, `lastname`)
VALUES (1, 'Bob', 'Smith'), (2, 'John','Doe'), (3, 'Jane','Doe'), (4, 'Sam','Foo'), (5, 'Emily','Bar');

INSERT INTO `projects` (`projectid`, `locationid`)
VALUES (1, 51), (2, 12), (3,15), (4,51), (5,99), (6,21), (7,51);

INSERT INTO `schedule` (`scheduleid`, `agentid`, `projectid`)
VALUES (1, 1, 1), (2, 2, 3), (3, 4, 3), (4, 1, 6), (5, 3, 5), (6, 5, 1), (7, 5, 3), (8, 5, 7), (9, 3, 6), (10, 4, 4);

Answer 1

假设 LEFT OUTER 连接是必要的(我不相信这一点)，那么您想要的代理列表不在上面获得的列表中。因此:

SELECT agents.agentid, agents.firstname, agents.lastname
  FROM agents
 WHERE agentid NOT IN
       (SELECT agents.agentid
          FROM agents
          LEFT JOIN schedule USING (agentid)
          LEFT JOIN projects USING (projectid)
         WHERE (projects.locationid = 51)
       )
 ORDER BY agentid;

显然，如果您想知道这些代理参与了哪些项目，那么您可以将连接恢复到外部查询的 FROM 子句中。