title: TSQL basic join on Northwind database
body: <p>I am learning TSQL (well, just SQL to tell the truth) and I want to make Employee - Product statistic on Northwind database.</p>
  
  <p>Expected results should be something like:</p>
  
  <pre><code>    EmployeeID | ProductID | income
      1          |         1 |    990
      1          |         2 |    190
      1          |         3 |      0
  ...
  </code></pre>
  
  <p>For all Employy-Product pairs</p>
  
  <p>My first try is this query:</p>
  
  <pre><code>SELECT E.EmployeeID, OE.ProductID, SUM(OE.ExtendedPrice) as income
  FROM [Order Details Extended] OE
  JOIN [Orders] O
  ON OE.OrderID = O.OrderID
  RIGHT OUTER JOIN Employees E
  ON E.EmployeeID = O.EmployeeID
  GROUP BY E.EmployeeID, OE.ProductID
  ORDER BY E.EmployeeID
  </code></pre>
  
  <p>But I don't get results for all pairs.
  What am I doing wrong?</p>
  
  <p>HLGEM missed few columns but I understood what he tried to do.
  I came up with this:</p>
  
  <pre><code>SELECT A.employeeid, A.productid, SUM(Oe.ExtendedPrice) AS income
  FROM
      (SELECT E.Employeeid, P.productid 
      FROM employees E
      CROSS JOIN products P) A
  LEFT JOIN  [Order Details Extended] OE 
      ON A.productid  = OE.productid  
  LEFT JOIN [Orders] O 
      ON OE.OrderID = O.OrderID
  GROUP BY A.EmployeeID, A.ProductID 
  ORDER BY A.EmployeeID, A.ProductID
  </code></pre>
  
  <p>This returns results for all pairs, but those don't seem right.
  For example above query returns as first row:</p>
  
  <pre><code>1, 1, 12788.10
  </code></pre>
  
  <p>But this query:</p>
  
  <pre><code>SELECT SUM(ODE.ExtendedPrice) FROM [Order Details Extended] ODE
  LEFT JOIN [Orders] OD
  ON ODE.OrderID = OD.OrderID
  WHERE OD.EmployeeID = 1 AND ODE.ProductID = 1
  </code></pre>
  
  <p>Returns 990.90.</p>
  
  <p>Why?</p>
  
  <h2>edit</h2>
  
  <p>I got it finally:</p>
  
  <pre><code>SELECT A.EmployeeId, A.ProductId, ISNULL(SUM(Oe.ExtendedPrice), 0) AS income
  FROM
      (SELECT E.Employeeid, P.productid 
      FROM [Employees] E
      CROSS JOIN [Products] P) A
  LEFT JOIN [Orders] O 
      ON O.EmployeeID = A.EmployeeID
  LEFT JOIN  [Order Details Extended] OE 
      ON A.productid  = OE.productid  AND OE.OrderID = O.OrderID
  GROUP BY A.EmployeeID, A.ProductID 
  ORDER BY A.EmployeeID, A.ProductID
  </code></pre>
  
  <p>@HLGEM you can copy/paste this solution to your answer so I can accept it.</p>