mysql - 如何使用自连接获取不匹配的记录

Question

假设我有一个名为 users 的表，其中包含以下列:

 user_id (int), 
 job_id (int),
 created (date)

我想抓取两个用户，根据job_id得到不匹配的记录。

例子

user_id  | job_id | created 

15242    |  234   | 2015-04-07 
15242    |  441   | 2015-04-08
15242    |  345   | 2015-04-08
24521    |  234   | 2015-04-09

我想获得 job_ids 441 和 345。

所以需要自连接

SELECT users.job_id
FROM users as switch_from
LEFT JOIN users as switch_to ON switch_from .job_id = switch_to .job_id
WHERE switch_from.user_id = 15242 AND switch_to.user_id = 24521;

这难道不应该给我别名表 switch_from 中缺少的所有 job_ids 吗？

这将返回具有匹配 job_id 的唯一行。

Answer 1

像这样尝试:

SELECT switch_from.job_id
FROM users as switch_from
LEFT JOIN users as switch_to ON switch_from.job_id = switch_to.job_id AND switch_to.user_id = 24521
WHERE switch_from.user_id = 15242 AND switch_to.user_id IS NULL;