TSQL basic join on Northwind database

I am learning TSQL (well, just SQL to tell the truth) and I want to make Employee - Product statistic on Northwind database.

Expected results should be something like:

    EmployeeID | ProductID | income
    1          |         1 |    990
    1          |         2 |    190
    1          |         3 |      0
...

For all Employy-Product pairs

My first try is this query:

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

But I don't get results for all pairs. What am I doing wrong?

HLGEM missed few columns but I understood what he tried to do. I came up with this:

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

This returns results for all pairs, but those don't seem right. For example above query returns as first row:

1, 1, 12788.10

But this query:

SELECT SUM(ODE.ExtendedPrice) FROM [Order Details 开发者_JS百科Extended] ODE
LEFT JOIN [Orders] OD
ON ODE.OrderID = OD.OrderID
WHERE OD.EmployeeID = 1 AND ODE.ProductID = 1

Returns 990.90.

Why?

edit

I got it finally:

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

@HLGEM you can copy/paste this solution to your answer so I can accept it.

You could try:

SELECT A.employeeid,A.product_id, SUM(Oe.ExtendedPrice) AS income
FROM
    (SELECT E.Employeeid, P.product id 
    FROM employee E
    CROSS JOIN product p) A
LEFT JOIN  [Order Details Extended] OE 
    ON A.EmployeeID = O.EmployeeID
LEFT JOIN [Orders] O 
    ON OE.OrderID = O.OrderID  
GROUP BY A.EmployeeID, OE.ProductID 
ORDER BY A.EmployeeID

I switched it to a LEFT JOIN as most people use them instead of right joins and thus they are easier for maintenance.

What results are you missing? It looks like you have used an inner join between Order Details Extended and Orders, so any orders that don't have details will be excluded. It seems logical that you would want this, since you are summing a value in the details table.

Then you do a right outer join with Employees, so you are including all employees, regardless of whether they have any orders. This also makes sense, since it looks as though you are seeing which employees sold which products.

Your query will only give you the data for products that the employees have actually sold. If you are looking for all employee-product combinations, regardless of whether the employee had ever sold that product, you will want to join the Product table as well, although in my muddled morning, I can't think if you would want an outer join or a cross join.

In all, I think your first try is a good one. Pretty complex for a beginner. Nice effort!