Sql-server – Return All Data From Left Table

sql serversql-server-2008-r2t-sql

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;

Best Answer

If you add a filter on the right table, you effectively turn your outer join into an inner join.

Move the date filter from WHERE to ON.

Also I suggest an open-ended range rather than BETWEEN.

Finally, I suggest not naming your CTE the same name as the base table (this is confusing), using proper schema references, and end with a semi-colon.

Here's a start:

;With ec As
(
    Select
      StoreName
      ,DataFromOne = Count(ramalad)
    FROM dbo.DataFromOne
    WHERE EntryDate >= '20170101' 
       AND EntryDate < '20170401'
   GROUP BY StoreName
)
Select 
  ec.StoreName
  ,[Emp Count] = COUNT(fji.Employee)
  ,[PT Count] =      COALESCE(ec.DataFromOne,0)
FROM ec
LEFT OUTER JOIN dbo.DataFromTwo AS fji
   ON ec.StoreName = fji.StoreName
   AND fji.SaleDate >= '20170101'
   AND fji.SaleDate <  '20170401'
GROUP BY ec.StoreName, ec.DataFromOne
ORDER BY ec.StoreName;

In fact, you can simplify the query logic quite a bit (though I am not sure exactly how that will change your plan):

SELECT 
  ec.StoreName,
  [Emp Count] = COUNT(fji.Employee),
  [PT Count]  = COUNT(ec.ramalad)
FROM dbo.DataFromOne AS ec
LEFT OUTER JOIN dbo.DataFromTwo AS fji
   ON ec.StoreName = fji.StoreName
   AND fji.SaleDate >= '20170101'
   AND fji.SaleDate <  '20170401'
WHERE ec.EntryDate >=  '20170101' 
  AND ec.EntryDate <   '20170401'
GROUP BY ec.StoreName
ORDER BY ec.StoreName;
Related Question