[SOLVED] Give the names of employees who sell the products of more than 10 suppliers

Give the names of employees who sell the products of more than 10 suppliers

I am finding it difficult to write the query for the above question I have written the following query but it is returning all the rows

SELECT e.EmployeeID 
FROM employees e 
JOIN orders o ON e.EmployeeID = o.EmployeeID 
JOIN order_details od ON o.OrderID = od.OrderID 
JOIN Products p ON od.ProductID = p.ProductID 
GROUP BY e.EmployeeID 
HAVING COUNT(p.SupplierID) > 10

Solution

Your filtering condition is:

HAVING COUNT(p.SupplierID) > 10

COUNT() counts the number of non-NULL values. Presumably, all products have a supplier and you are using inner joins. So, all matching rows get counted. It is equivalent to:

HAVING COUNT(*) > 10

And this counts the number of order lines for a given employee.

You want to count distinct suppliers, not order lines. The simplest method is to use COUNT(DISTINCT):

HAVING COUNT(DISTINCT p.SupplierID) > 10

Because you are learning SQL, I would advise you to understand this version as well:

SELECT e.EmployeeID
FROM (SELECT e.EmployeeID, p.SupplierID, COUNT(*) as num_ordelines
      FROM employees e JOIN
           orders o
           ON e.EmployeeID = o.EmployeeID JOIN
           order_details od
           ON o.OrderID = od.OrderID JOIN
           Products p
           ON od.ProductID = p.ProductID 
      GROUP BY e.EmployeeID, p.SupplierId
     ) es 
GROUP BY EmployeeID
HAVING COUNT(SupplierID) > 10

This returns the same result set (assuming SupplierId is never NULL). The subquery has one row per employee/supplier. The outer query then counts these rows.