#1064 - 您的 SQL 语法有错误;靠近 'WHERE e.company_id = 1

Question

我正在尝试在MYSQL 中运行Left Join

SELECT e.id, e.employee_code, concat(e.first_name,' ',e.last_name) Fullname, e.email, e.department_id, 
CASE
WHEN a.line_manager_mid_year_approved = 0 THEN "DRAFT"
WHEN a.line_manager_mid_year_approved = 1 THEN "AWAITING APPROVAL"
WHEN a.line_manager_mid_year_approved = 2 THEN "NOT APPROVED"
WHEN a.line_manager_mid_year_approved = 3 THEN "APPROVED"
ELSE "NOT STARTED"
END AS line_manager_mid_year_approved,
e.grade_level_name, e.work_location_id, e.line_manager_id
FROM hr_employees e
LEFT JOIN 
(SELECT employee_id, line_manager_mid_year_approved FROM appraisal_goals GROUP BY employee_id) a
ON a.employee_id = e.id
WHERE a.is_active = 1
WHERE e.company_id = 1 
AND e.hr_status = 0 
AND e.validation_status = 'VALID'
AND e.employee_type_code NOT IN (4,5);

我得到了这个错误：

#1064-您的SQL语法有错误；查看与MariaDB服务器版本对应的手册，了解要使用的正确语法接近'WHERE e.company_id=1 AND e.hr_status=0 ANDe.validation_status=第15行处的"VALID">

当我删除WHERE a.is_active=1时，错误不再存在。但我真的需要那个特殊的where条款。

如何解决此问题？

感谢

Answer 1

您似乎使用了多个WHERE子句。

尝试将第二个WHERE更改为AND:

SELECT e.id, e.employee_code, concat(e.first_name,' ',e.last_name) Fullname, e.email, e.department_id, 
CASE
WHEN a.line_manager_mid_year_approved = 0 THEN "DRAFT"
WHEN a.line_manager_mid_year_approved = 1 THEN "AWAITING APPROVAL"
WHEN a.line_manager_mid_year_approved = 2 THEN "NOT APPROVED"
WHEN a.line_manager_mid_year_approved = 3 THEN "APPROVED"
ELSE "NOT STARTED"
END AS line_manager_mid_year_approved,
e.grade_level_name, e.work_location_id, e.line_manager_id
FROM hr_employees e
LEFT JOIN 
(SELECT employee_id, line_manager_mid_year_approved FROM appraisal_goals GROUP BY employee_id) a
ON a.employee_id = e.id
WHERE a.is_active = 1
AND e.company_id = 1 
AND e.hr_status = 0 
AND e.validation_status = 'VALID'
AND e.employee_type_code NOT IN (4,5);

或者，您可以将WHERE a.is_active = 1作为ON子句的一部分，例如：

SELECT e.id, e.employee_code, concat(e.first_name,' ',e.last_name) Fullname, e.email, e.department_id, 
CASE
WHEN a.line_manager_mid_year_approved = 0 THEN "DRAFT"
WHEN a.line_manager_mid_year_approved = 1 THEN "AWAITING APPROVAL"
WHEN a.line_manager_mid_year_approved = 2 THEN "NOT APPROVED"
WHEN a.line_manager_mid_year_approved = 3 THEN "APPROVED"
ELSE "NOT STARTED"
END AS line_manager_mid_year_approved,
e.grade_level_name, e.work_location_id, e.line_manager_id
FROM hr_employees e
LEFT JOIN 
(SELECT employee_id, line_manager_mid_year_approved FROM appraisal_goals GROUP BY employee_id) a
ON a.employee_id = e.id
AND a.is_active = 1
WHERE e.company_id = 1 
AND e.hr_status = 0 
AND e.validation_status = 'VALID'
AND e.employee_type_code NOT IN (4,5);