Return all rows using Count statement and Left Join with a Where Clause

Question

I am running the following

Select count (b.confirm_cd) cd_Instances,A.EID,A.Firstname, A.LastName, b.Agent_Login, A.AgentOrg from Prod.Employee A 
left outer join Prod.CAPTURE_DETAILS b
on a.AgentID=b.Agent_ID 
Where b.date>'2022-07-31'
group by A.EID, A.Firstname, A.LastName, b.Agent_Login, A.AgentOrg order by cd_Instances asc

The b.date does not exist in Prod.Employee, therefore I only return results for agents which exist in the capture details table. This works correctly without the where clause but I need to be able to filter by date and still receive the results with a null count in capture details.

score 1 · Answer 1 · answered Aug 30 '22 at 15:53

If you have a criteria in your where clause based on data from a table to which you are left joining that turns it into an inner join. Instead, put the criteria related to the table to which you are left joining in the join clause.

SELECT
                COUNT (b.confirm_cd) cd_Instances
              , A.EID
              , A.Firstname
              , A.LastName
              , b.Agent_Login
              , A.AgentOrg
FROM            Prod.Employee        A
LEFT OUTER JOIN Prod.CAPTURE_DETAILS b ON A.AgentID = b.Agent_ID
                                      AND b.date > '2022-07-31'
GROUP BY        A.EID
              , A.Firstname
              , A.LastName
              , b.Agent_Login
              , A.AgentOrg
ORDER BY        cd_Instances ASC;

Return all rows using Count statement and Left Join with a Where Clause

1 Answers1