2

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;
9

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;
  • Thank you for the link about avoiding Between! Also, I love the second syntax you posted removing the CTE! That one speeds up the completion quite a bitl – IcyPopTarts May 11 '17 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.