mysql 查询从子查询迁移

Question

您好，我有以下工作查询，但我希望摆脱子查询方法。

select t0.emp_id, t1.first_name, t1.last_name

from employee_profile As t0 left join user_profile as t1 on t0.user_profile_id = t1.id

where t0.emp_id not in (select t4.emp_id from time_sheet as t4 where t4.ts_end_date = '2014-10-08')

共有 1301 名员工资料有 337 个没有结束日期 2014-10-08 的 time_sheets

到目前为止，这工作得很好。

现在我正在尝试从子查询迁移到联接。我尝试了以下方法但没有成功。

通过此查询，我得到的结果与我正在寻找的结果完全相反，即 964 个结果。

select t0.emp_id, t1.first_name, t1.last_name, t3.emp_id

from employee_profile As t0 left join user_profile as t1 on t0.user_profile_id = t1.id 
left join time_sheet as t3 on t0.id = t3.employee_profile_id

我还尝试了以下使用 where 条件并得到 0 个结果

1.

where t3.ts_end_date = '2014-10-08'
and t3.employee_profile_id is null

2.

where t3.ts_end_date = '2014-10-08'
and t3.id is null

有人知道如何解决这个问题吗？

Answer 1

明白了

select t0.emp_id, t1.first_name, t1.last_name, t3.emp_id

from employee_profile As t0 left join user_profile as t1 on t0.user_profile_id = t1.id 
left join time_sheet as t3 on t0.id = t3.employee_profile_id and t3.ts_end_date = '2014-10-08'

where t3.employee_profile_id is null