SQL: Get count using a JOIN (Grouping before the Join)

We need to display a count of all projects and labels that are batched even if the count is 0.

In this example: there is a batch view LabelBatchView and a list of projects and designs view ProjectLabelsView.

Using  Group By on the BatchView limits the data to what is actually in the batch.  Using a sub-query gives the workaround of not having to include the fields in the Group By and gives me the desired results:

SELECT a.ProjectID, a.ProjectName, a.LabelDesignID, a.LabelDesignDescription, 
COALESCE (b.LabelCount, 0) AS LabelCount 
    FROM ProjectLabelsView as a 
   LEFT OUTER JOIN (SELECT LabelCount, ProjectID, LabelDesignID FROM LabelBatchView) AS b
       ON a.ProjectID = b.ProjectID AND a.LabelDesignID = b.LabelDesignID
   ORDER BY a.ProjectName

Leave a thought