2

I have 4 tables.... Employees, Customers, Orders and Order_Info. I am trying to inner join the 4 tables to sum up the order amounts and calculate the employees commission based on 7%. I am very close to solving this but I have one slight problem: I am not getting all of the employees because less are showing up than exist in my Employees table. This is how I currently have my query written:

SELECT Employees.lName, Employees.fName,
       SUM(quantOrdered * costEach) AS ttl_orders_value,
       (SUM(quantOrdered * costEach) * .07) AS Commission
FROM Customers
INNER JOIN Employees ON Customers.empNumber = Employees.empNumber 
INNER JOIN Orders ON Customers.custNumber = Orders.custNumber 
INNER JOIN Order_Info ON Orders.ordNumber = Order_Info.ordNumber
GROUP BY Employees.lName, Employees.fName 
ORDER BY Employees.lName, Employees.fName

I wish to get all employees even if their commission and total sales equal zero, which I believe to be calculated from NULLS.

Any help improving my query would be greatly appreciated!

finiteloop
  • 483
  • 4
  • 14

1 Answers1

2

try this:

SELECT Employees.lName, Employees.fName,
       SUM(ISNULL(quantityOrdered,0) * ISNULL(priceEach,0)) AS ttl_orders_value,
       (SUM(ISNULL(quantOrdered,0) * ISNULL(costEach,0)) * .05) AS Commission
FROM Employees 
LEFT JOIN Customers ON Customers.empNumber = Employees.empNumber 
LEFT JOIN Orders 
   INNER JOIN OrderDetails ON Orders.ordNumber = OrderDetails.ordNumber
ON Customers.custNumber = Orders.custNumber
WHERE Employees.workTitle = 'Developer' 
GROUP BY Employees.lName, Employees.fName 
ORDER BY Employees.lName, Employees.fName

Note, I have changed INNER JOIN to LEFT JOIN only for Orders table because as you say Employees records should be there, they only may not have linked orders.

You may also need to wrap NULL priceEach, costEach, quantityOrdered and quantOrdered values with ISNULL([field_name], 0) to get proper results for those employees not having any orders.

andrews
  • 2,173
  • 2
  • 16
  • 29
  • That gave me the same result – finiteloop Nov 30 '16 at 08:11
  • How would I perform that Wrap? – finiteloop Nov 30 '16 at 08:12
  • @finiteloop see the updated answer, I have modified the join to OrderDetails and also added ISNULL wrappers. – andrews Nov 30 '16 at 08:14
  • @andrews I never knew that nested `LEFT JOIN` & `INNER JOIN` was a thing. That's pretty neat. Is it accepted as a standard? – Jens Nov 30 '16 at 08:16
  • @andrews I still get the same result 15 records. Tricky! – finiteloop Nov 30 '16 at 08:17
  • @finiteloop then we do need to input data, table structure and the desired result. It's hard to suggest without all data. – andrews Nov 30 '16 at 08:19
  • @finiteloop do you have employees without a customer perhaps? – Jens Nov 30 '16 at 08:19
  • @Jens nested left join and inner join has worked for me locally, if we dont move that inner join to OrderDetails under Orders we will not get any result for customers without any Orders because inner join to OrderDetails will cut them off. – andrews Nov 30 '16 at 08:21
  • @andrews yes, I understand that, but I never knew you could include an `INNER JOIN` into a `LEFT JOIN` like that. I'd always ended up writing 2 `LEFT JOIN`'s in the past. This seems like a way more elegant solution though. – Jens Nov 30 '16 at 08:25
  • @finiteloop see the updated answer, I have reversed Employees and Customers in the FROM clause and added left JOIN to Customers because u say u can have Employees without customers. – andrews Nov 30 '16 at 08:27
  • @andrews That did the trick! One last follow up if I may, I appreciate all your help! If I wanted to add a where clause for the employee table to specify their workTitle how would I go about that? – finiteloop Nov 30 '16 at 08:37
  • @finiteloop, added the WHERE clause for Employees, see the updated answer. Don't forget to accept the answer and vote up ;). – andrews Nov 30 '16 at 08:43
  • @andrews That was a huge help! Thank you for all the effort! Dealing with 4 tables and these joins is a task! – finiteloop Nov 30 '16 at 08:44
  • 1
    @finiteloop you are welcome! Use this small snippet to make your way through future JOIN tasks. Notice, how the nested INNER JOIN to OrderDetails was used which is a nice trick as Jens have pointed out. – andrews Nov 30 '16 at 08:45
  • @andrews I definitely will! I realized I named a couple columns inconsistenly in my original question, so I changed it above just an FYI! I also found that the query builder really helped me to have a solid starting point to ask this question. I am thankful that you were able to piece it together so well with such limited details! Also, thanks jens ! :) – finiteloop Nov 30 '16 at 08:50