时间戳

首页 > 解决方案 > MySQL LEFT JOIN 忽略 AND 条件

mysql - MySQL LEFT JOIN 忽略 AND 条件

问题描述

我有一个功能性的 LEFT JOIN MySQL 查询，结构如下：

SELECT 
    COUNT(HTG_ScheduleRequest.ID) AS current_job,
    HTG_TechProps.EmpNumber, 
    HTG_TechProps.EmpFirstName, 
    HTG_TechProps.EmpLastName,  
    HTG_TechProps.Veh_Number
     FROM HTG_TechProps 
        LEFT JOIN HTG_ScheduleRequest ON HTG_TechProps.EmpNumber = HTG_ScheduleRequest.SSR 
        AND (HTG_ScheduleRequest.ScheduleDateCurrent = CURDATE() || HTG_ScheduleRequest.ScheduleDateExact = CURDATE())
        AND RecordType = '1' 
        AND HTG_ScheduleRequest.JobStatus IN (2,5,8,3,4,7)   
        GROUP BY HTG_TechProps.EmpNumber ORDER BY HTG_TechProps.EmpNumber ASC

我需要向初始 SELECT 表添加一些条件，如下所示：

HTG_TechProps.EmpStatus='A'

当我在 LEFT JOIN 之前添加 WHERE 语句时出现语法错误，并且当我在 LEFT JOIN 之后添加这样的 AND 时，它会被忽略，仍然返回不等于 A 的记录。

LEFT JOIN HTG_ScheduleRequest ON HTG_TechProps.EmpNumber = HTG_ScheduleRequest.SSR      
AND HTG_TechProps.EmpStatus='A'

标签： mysqlsql

解决方案

a 中第一个表的条件LEFT JOIN应该放在一个WHERE子句中：

SELECT COUNT(sr.ID) AS current_job,
       tp.EmpNumber, tp.EmpFirstName, tp.EmpLastName, tp.Veh_Number
FROM HTG_TechProps tp LEFT JOIN
     HTG_ScheduleRequest sr
     ON tp.EmpNumber = sr.SSR AND
        (sr.ScheduleDateCurrent = CURDATE() OR sr.ScheduleDateExact = CURDATE()
        ) AND
        sr.RecordType = '1' AND  -- assume this comes from SR
        sr.JobStatus IN (2, 5, 8, 3, 4, 7)   
WHERE tp.EmpStatus='A'
GROUP BY tp.EmpNumber  -- this is okay assuming that it is unique or (equivalently) a primary key
ORDER BY tp.EmpNumber ASC;

请注意，这引入了表别名，因此查询更易于编写和阅读。

推荐阅读