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