Skip to content
Advertisement

LEFT JOIN returning no results if a WHERE clause is added?

DECLARE @ps_StartDate DATETIME = '20190101'
      , @ps_EndDate   DATETIME = '20191224'
      , @ps_COMPANYNO VARCHAR(20) = '01'

SET @ps_EndDate = DATEADD(dd, 1, @ps_EndDate)

SELECT
    CASE WHEN @ps_COMPANYNO IN ('01') THEN 'YS45J146'
        ELSE 'YS45J147'
    END                                 AS 'Client ID'
    ,e.EMPNO                            AS 'WORKER ID'
    ,''                                 AS 'Org'
    ,''                                 AS 'Job Number'
    ,'Regular'                          AS 'Pay Component'
    ,''                                 AS 'Rate'
    ,''                                 AS 'Rate Number'
    ,SUM(t.TIMEBILLED + (ISNULL(ehrs.AllocationDays, 0) * 24))                  AS 'Hours'
    ,''                                 AS 'Units'
    ,''                                 AS 'Line Date'
    ,''                                 AS 'Amount'
    ,''                                 AS 'Check Seq Number'
    ,''                                 AS 'Override State'
    ,''                                 AS 'Override Local'
    ,''                                 AS 'Override Local Jurisdiction'
    ,''                                 AS 'Labor Assignment'
FROM TIMEBILL t (NOLOCK)
INNER JOIN EMPLOYEE e (NOLOCK) ON t.USERID = e.USERID
LEFT JOIN dbo.EmpHRSchedule ehrs (NOLOCK) ON (e.EmployeeNo = ehrs.EmployeeNo)
WHERE t.PRTID = 'P'
    AND t.DATE >= @ps_StartDate
    AND t.DATE < @ps_EndDate
    AND ehrs.STARTDATE >= @ps_StartDate
    AND ehrs.STARTDATE < @ps_EndDate
    AND e.COMPANYNO = @ps_COMPANYNO
    AND t.SUBCAT <> 'LUNCH'
    AND t.WAGETYPE <> 'OVERTIME'
    AND e.SALARYUNIT = 'HOURLY'
    --AND ehrs.Type = 'HOLIDAY'
GROUP BY e.EMPNO

I’m trying to add the dbo.EmpHRSchedule.Type = ‘HOLIDAY’ entries to the Hours column only if they exist. To my understanding, a LEFT JOIN would only return entries if they match, and return NULL for non-matches. When I uncomment the AND ehrs.Type = 'HOLIDAY' line, no results are returned at all.

Why is this the case? Is there another way I should be going about this to sum the dbo.Timebill.Timebilled and dbo.EmpHRSchedule.AllocationDays (if they exist)?

Advertisement

Answer

When you include:

AND ehrs.Type = 'HOLIDAY'

in the WHERE clause, you actually get an INNER join instead of a LEFT join because you get only the matching rows from the table ers and not any of the unmatched rows for which ehrs.Type would be NULL.
What you can do is move the condition to the ON clause of the LEFT join:

LEFT JOIN dbo.EmpHRSchedule ehrs (NOLOCK) 
ON (e.EmployeeNo = ehrs.EmployeeNo) AND ehrs.Type = 'HOLIDAY' 

and you could also include the other conditions for the same table:

LEFT JOIN dbo.EmpHRSchedule ehrs (NOLOCK) 
ON (e.EmployeeNo = ehrs.EmployeeNo) AND ehrs.Type = 'HOLIDAY' 
   AND ehrs.STARTDATE >= @ps_StartDate
   AND ehrs.STARTDATE < @ps_EndDate
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement