MySQL - 将查询结果与当前表数据进行比较

Question

我有以下两个表:

表1

|  id |  name |  job    |      start_time      |     end_time          |
| ----| ------| --------| ---------------------| ----------------------|
| 111 |  John |  Janitor|  2016-08-20 00:01:00 |       NULL            |
| 111 |  John |  Janitor|        NULL          |   2016-08-20 00:02:00 |
| 222 |  Sam  |  Valet  |  2016-08-20 00:03:00 |          NULL         |
| 222 |  Sam  |  Valet  |        NULL          |   2016-08-20 00:04:00 |

表2

|  name |  job    |      checkin_time    |
| ------| --------| ---------------------|
|  John |  Janitor|  2016-08-20 00:01:30 |
|  Sam  |  Valet  |  2016-08-20 00:03:30 |
|  Tim  |  Cashier|  2016-09-20 00:01:00 |

以下查询

SELECT id, Table2.name, Table2.job, start_time, Table2.checkin_time, end_time FROM (
SELECT id,name,job, MIN(start_time) AS start_time, MAX(end_time) AS end_time
FROM Table1
GROUP BY id
) AS results INNER JOIN Table2 ON 
  results.job = Table2.job
  AND results.name = Table2.name
  AND (Table2.checkin_time BETWEEN results.start_time AND results.end_time OR
       Table2.checkin_time >= results.start_time AND results.end_time IS NULL);

将显示:

|  id |  name |  job    |      start_time      |     checkin_time    |  end_time    |       
| ----| ------| --------| ---------------------| --------------------|----------------|
| 111 |  John |  Janitor|  2016-08-20 00:01:00 | 2016-08-20 00:01:30 |2016-08-20 00:02:00 |
| 222 |  Sam  |  Valet  |  2016-08-20 00:03:00 | 2016-08-20 00:03:30 |2016-08-20 00:04:00 |

如何制定查询以便返回未成功/未找到的记录。例如。从表1中，记录为:

|  Tim  |  Cashier|  2016-09-20 00:01:00 |

提前感谢您社区团队的帮助!

Answer 1

您可以进行右连接并使用空值仅过滤“不匹配”:

SELECT table2.*
FROM (
    SELECT id,name,job, MIN(start_time) AS start_time, MAX(end_time) AS end_time
    FROM Table1
    GROUP BY id) AS results
RIGHT JOIN Table2 ON 
    results.job = Table2.job
    AND results.name = Table2.name
    AND (Table2.checkin_time BETWEEN results.start_time AND results.end_time OR
        Table2.checkin_time >= results.start_time AND results.end_time IS NULL)
WHERE results.id IS NULL