mysql - 如何获取具有不同/唯一 id 且左连接中没有记录的行

Question

我正在尝试返回包含联系人 ID 和姓名的记录列表。每个联系人只有 1 个，并且该联系人未分配给我尝试获取其列表的任务。

我有 2 个表格，对于这个问题，我删除了所有不相关的列。

表 1:联系人

+---------------------+-----------+
|   id (primary, AI)  |   name    | 
+---------------------+-----------+
|   1                 |   John    |
|   2                 |   Mary    |
|   3                 |   Jeff    |
|   4                 |   Bill    |
|   5                 |   Bob     |
+---------------------+-----------+

表 2:任务

+------------+-----------------+
|   task_id  |   contact_id    | 
+------------+-----------------+
|     2      |        1        |
|     2      |        2        |
|     1      |        1        |
|     3      |        1        |
|     4      |        1        |
+------------+-----------------+

我已经尝试了几种方法，但没有得到我想要的东西。

在下面的示例中，我尝试获取 task_id 2 的列表。

下面的查询很接近，因为“Mary”已经被分配，现在没有显示在结果中，但“John”仍然在那里，因为他被分配给其他任务。对于我在结果中添加任务列的示例，这是不需要的。

SELECT c.name as name, c.id as contact_id, t.task_id as task 
FROM contacts as c 
LEFT JOIN tasks as t ON t.contact_id = c.id AND t.task_id != 2 

返回:

+-------+-----------+--------+
|   id  |   name    |  task  |
+-------+-----------+--------+
|   1   |   John    |   1    |
|   3   |   Jeff    |  null  |
|   4   |   Bill    |  null  |
|   5   |   Bob     |  null  |
|   1   |   John    |   3    |
|   1   |   John    |   4    |
+-------+-----------+--------+

如果我将 AND t.task_id != 2 部分移动到 WHERE，我只会得到 'John':

SELECT c.name as name, c.id as contact_id, t.task_id as task 
FROM contacts as c 
LEFT JOIN tasks as t ON t.contact_id = c.id 
WHERE t.task_id != 2 

返回:

+-------+-----------+--------+
|   id  |   name    |  task  |
+-------+-----------+--------+
|   1   |   John    |   1    |
|   1   |   John    |   3    |
|   1   |   John    |   4    |
+-------+-----------+--------+

Answer 1

如果您想要的是表 contacts 中的所有行，而表 tasks 中没有 task_id = 2 的行您可以使用NOT EXISTS:

select * from contacts c
where not exists (
  select 1 from tasks t
  where t.contact_id = c.id and t.task_id = 2
)

或者使用NOT IN:

select * from contacts
where id not in (select contact_id from tasks where task_id = 2)

请参阅demo .
结果:

> id | name
> -: | :---
>  3 | Jeff
>  4 | Bill
>  5 | Bob