How to get a column from a table that corresponds to another table

querysyntax

I have these tables:

OrderDetails:
enter image description here

Orders:
enter image description here

Employees:
enter image description here

Products:
enter image description here

Customers:
enter image description here

I want to write a query where I can get the last name of the employee with the largest order quantity. I managed to write these following queries:

'Selects biggest purchase from OrderDetails'
SELECT MAX(Quantity) FROM [OrderDetails];

'Selects the OrderID of orders'
SELECT OrderID FROM [OrderDetails];

SELECT EmployeeID FROM [Orders]
SELECT OrderID FROM [Orders]

But I don't know how to combine them.

Best Answer

As I'm not sure whether you're looking for the person who sold the most of a thing on an individual order, or the person who sold the most of a thing over a period of time, here is a rough query that will allow you to see both:

SELECT e.LastName, tmp.MaxQuantity, tmp.SumQuantity
  FROM Employees e INNER JOIN (SELECT o.EmployeeID, MAX(od.Quantity) as MaxQuantity, SUM(od.Quantity) as SumQuantity
                                 FROM Orders o INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
                                GROUP BY o.EmployeeID) tmp ON e.EmployeeID = tmp.EmployeeID
 ORDER BY tmp.MaxQuantity DESC;

If you want to know who sold the most of a product on a single OrderDetail line, use ORDER BY tmp.MaxQuantity DESC. If you want to know who has sold the most of a product ever, use ORDER BY tmp.SumQuantity DESC`.

Be sure to edit this query to:

  • limit the number of rows to something reasonable
  • limit the date ranges to something reasonable (unless you really do want 25 years of data)
  • take into account people who may no longer be in the Employees table if they have left the company

Edit to show a possible "filter by country" option:

If you are interested in filtering by country, or some other Customer attribute, you can add the Customers table to the subquery like so:

SELECT e.LastName, tmp.MaxQuantity, tmp.SumQuantity
  FROM Employees e INNER JOIN (SELECT o.EmployeeID, MAX(od.Quantity) as MaxQuantity, SUM(od.Quantity) as SumQuantity
                                 FROM Customers cu INNER JOIN Orders o ON cu.CustomerID = o.CustomerID
                                                   INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
                                WHERE cu.CountryCode = 'DE'
                                GROUP BY o.EmployeeID) tmp ON e.EmployeeID = tmp.EmployeeID
 ORDER BY tmp.MaxQuantity DESC;

Note: I do not know the structure of your Customers table, so be sure to edit this accordingly.

If your Customers table uses the full country name, you may want to double check that there are no "short forms" that create discrepancies in the data. A good example of this would be "United States of America", which could be written in full, or as "USA" or "U.S.A." or "US" or "U.S." and on and on and on. Depending on how your database is laid out, this could be a good opportunity to double-check that the data is consistent for the columns you wish to filter on.


Final edit to show something completely different from the original question:

SELECT p.ProductID, p.ProductName, p.CategoryID, c.CategoryName, p.Unit, COUNT(od.Quantity) as TotalOrdered
  FROM Categories c INNER JOIN Products p ON c.CategoryID = p.CategoryID
                    INNER JOIN OrderDetails od ON p.ProductID = od.ProductID
                    INNER JOIN Order o ON od.OrderID = o.OrderID
                    INNER JOIN Customers cu ON o.CustomerID = cu.CustomerID
 WHERE cu.Country = 'Germany' and o.OrderDate BETWEEN '1990-01-01' AND '2021-12-31'
 GROUP BY p.ProductID, p.ProductName, p.CategoryID, c.CategoryName, p.Unit
 ORDER BY TotalOrdered DESC
 LIMIT 25;

Note: This query will work on MySQL, MariaDB, and PostgreSQL. If you are using SQL Server, SQL Express, SQLite, Oracle, or some other database engine, you will need to adapt the syntax to match that system.