如果列中的值不为空，则 MySQL JOIN 条件

Question

查询

SELECT employee.emp_number as employee_id,
employee.termination_id,
employee.firstname as user_name,
termination.termination_date 
FROM employee
JOIN user ON user.emp_number = employee.emp_number 
JOIN reportto ON reportto.emp_number = employee.emp_number 
LEFT JOIN termination ON 
( termination.id = employee.termination_id 
  AND termination.termination_date > '2016-01-01') 
WHERE ohrm_user.created_by = '31' 
GROUP BY employee.emp_number

我在尝试什么

在 employee 表中，默认 termination_id 设置为 NULL。如果员工被终止，则 termination 表中的 id 被设置为值。我想让所有在特定日期之前完全没有被解雇或被解雇的员工。

上述查询获取所有结果，包括 termination_date 小于 2016-01-01 的结果。是否可以修改查询，使其忽略 termination_date 小于 2016-01-01 的结果？还是我必须执行 2 个不同的查询并合并结果才能实现此目的。

避免混淆的注意事项:我想要仍在工作(未终止)且未在 2016-01-01 之前终止的员工

Answer 1

试试下面的查询-

SELECT employee.emp_number AS employee_id,
employee.termination_id,
employee.firstname AS user_name,
termination.termination_date 
FROM employee
JOIN USER ON user.emp_number = employee.emp_number 
JOIN reportto ON reportto.emp_number = employee.emp_number 
LEFT JOIN termination ON 
( termination.id = employee.termination_id ) 
WHERE ohrm_user.created_by = '31' 
AND (employee.termination_id IS NULL OR termination.termination_date >= '2016-01-01') 
GROUP BY employee.emp_number