title: Table subsets comparison
body: <p>I am using the AdventureWorks database and SQL Server 2012 T-SQL Recipes book and got myself into trouble on the following example : </p>
  
  <p>I have to check the SalesQuota in both 2007 and 2008 from Sales.SalesPersonQuotaHistory.</p>
  
  <pre><code>SELECT sp.BusinessEntityID
  , SUM(s2008.SalesQuota) AS '2008'
  , SUM(S2007.SalesQuota) AS '2007'
  FROM Sales.SalesPerson sp
  LEFT OUTER JOIN Sales.SalesPersonQuotaHistory s2008
    ON sp.BusinessEntityID = s2008.BusinessEntityID
      AND YEAR(s2008.QuotaDate) = 2008
  LEFT OUTER JOIN Sales.SalesPersonQuotaHistory s2007
    ON sp.BusinessEntityID = s2007.BusinessEntityID
      AND YEAR(s2007.QuotaDate) = 2007
  GROUP BY sp.BusinessEntityID
  </code></pre>
  
  <p>First results are:</p>
  
  <pre><code>  BusinessEntityID 2008                  2007
  ---------------- --------------------- ---------------------
    274              1084000.00            1088000.00
    275              6872000.00            9432000.00
    276              8072000.00            9364000.00
    277              6644000.00            8700000.00
  </code></pre>
  
  <p>Just like the book says. </p>
  
  <p>But then I try to get the 2008 SalesQuota with the following query:</p>
  
  <pre><code>SELECT sp.BusinessEntityID,
         SUM(spqh.SalesQuota) AS '2008'
  FROM Sales.SalesPerson sp
    LEFT JOIN Sales.SalesPersonQuotaHistory spqh
     ON sp.BusinessEntityID = spqh.BusinessEntityID
       AND YEAR(spqh.QuotaDate) = 2008 
  GROUP BY sp.BusinessEntityID
  </code></pre>
  
  <p>and got this:</p>
  
  <pre><code> BusinessEntityID 2008
  ---------------- ---------------------
   274              271000.00
   275              1718000.00
   276              2018000.00
   277              1661000.00
  </code></pre>
  
  <p>What am I doing wrong here? I think I miss something on those LEFT JOINs but I can't figure out what.</p>
  
  <p><strong>This one gives the same result:</strong></p>
  
  <pre><code>SELECT BusinessEntityID
  , SUM(SalesQuota) AS '2008'
  FROM Sales.SalesPersonQuotaHistory
  WHERE YEAR(QuotaDate) = 2008 
  GROUP BY BusinessEntityID
  
  
   BusinessEntityID 2008
   ---------------- ---------------------
   274              271000.00
   275              1718000.00
   276              2018000.00
  </code></pre>