My left table holds more store names than the right table. I thought a quick left join would have that dataset returned, but to no avail!
Data In From clause has the most options and I want to show all of them Data In Left Join clause has some but not all of the options I want to show
I created the CTE to get a SUM() and pull the data into the main query If I use the CTE to display the storeName I get 15 returned that are null instead of the storeName(s) that exist in the From table that do not exist in the Left Join Table
Here is query I use that is not giving results I am after.
;With DataFromOne As
(
Select StoreName
,DataFromOne = Count(ramalad)
FROM DataFromOne
WHERE EntryDate BETWEEN '20170101' AND '20170331'
GROUP BY StoreName
)
Select ec.StoreName
,[Emp Count] = COUNT(Employee)
,[PT Count] = COALESCE(ec.DataFromOne,0)
FROM DataFromOne ec
LEFT OUTER JOIN DataFromTwo fji
ON ec.StoreName = fji.StoreName
WHERE fji.saleDate BETWEEN '20170101' AND '20170331'
GROUP BY ec.StoreName, ec.DataFromOne
ORDER BY ec.StoreName ASC;
DataFromOne.ramalad
nullable? – Aaron Bertrand May 11 '17 at 3:16