Derked Derked - 4 months ago 10
SQL Question

SQL Left Outer join with where clause reduces results from left outer join

I have the following query that works exactly how I would expect it to. It returns back all of the Statuses with the counts.

SELECT ProcessStatuses.Status, COUNT(SecretProcesses.ProcessStatusID) AS Count
FROM ProcessStatuses
LEFT OUTER JOIN SecretProcesses ON ProcessStatuses.ProcessStatusID = SecretProcesses.ProcessStatusID
GROUP BY ProcessStatuses.Status

Status Count
status1 0
status2 0
status3 0
status4 0
status5 0
status6 1
status7 0
status8 0


However if I add a WHERE clause to the query it returns back only the statuses that have counts. For example

SELECT ProcessStatuses.Status, COUNT(SecretProcesses.ProcessStatusID) AS Count
FROM ProcessStatuses
LEFT OUTER JOIN SecretProcesses ON ProcessStatuses.ProcessStatusID = SecretProcesses.ProcessStatusID
WHERE AreaID IN (21, 22, 23)
GROUP BY ProcessStatuses.Status

Status Count
status6 1


This kind of defeats the purpose of doing a left outer join since I want to be able to filter the results by Area that they reside in, while still displaying all of the possible statuses. The where clause is only returning statuses that have values, rather than all of them.

Answer

Assuming you still want to return all the statuses and AreaId is in the SecretProcesses table, you need to move the where criteria to the on condition because it is negating your outer join:

SELECT PS.Status, COUNT(SPProcessStatusID) AS Count
FROM ProcessStatuses PS
    LEFT JOIN SecretProcesses SP ON PS.ProcessStatusID = SP.ProcessStatusID 
        AND SP.AreaID IN (21, 22, 23)
GROUP BY PS.Status