How to get a column from a table that corresponds to another table [closed]

Question

I have these tables:

OrderDetails:

Orders:

Employees:

Products:

Customers:

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.

Answer 1

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.

Answer 2

To answer your initial question:

You should first start with the object you want to get data about, in this case Employees:

SELECT LastName
FROM Employees -- Gets the LastName of every employee

Now we need a way to pull in Orders information, since you want "the employee with the largest order quantity". The way we associate an Order record with an Employee record is with a JOIN operator. In this case an INNER JOIN is sufficient because INNER JOIN returns rows only where there is a match between both tables. (This will filter out Employees who have no Orders which is what we want since you're looking for largest order quantity.)

SELECT Employees.LastName -- Specifying the Table name or using an Alias is good practice to avoid errors when the same column exists in two tables that are part of the same query (not the case here but it's best practice to do for readability too).
FROM Employees
INNER JOIN Orders -- Returns a row for every Order for each Employee
    ON Employees.EmployeeId = Orders.EmployeeId

Now we need to do the same thing to bring in the OrderDetails which is where your Quantity data lives. Again an INNER JOIN should be appropriate here, like so:

SELECT Employees.LastName
FROM Employees
INNER JOIN Orders -- Returns a row for every Order for each Employee
    ON Employees.EmployeeId = Orders.EmployeeId
INNER JOIN OrderDetails -- Returns a row for every OrderDetail of every Order. 
    ON Orders.OrderID = OrderDetails.OrderID

Notice my comments in the query. Since the first INNER JOIN of the query returns a row for every Order of each Employee, and the second INNER JOIN returns a row for every OrderDetail of every Order, this whole query now returns one row per OrderDetail of every Order for each Employee, so your row count will be Employees x Orders x OrderDetails essentially.

Now that we have all rows for Employee Order Quantities with the above query, we want to order the data by largest Quantity descending and then filter out everything but the first record. We can accomplish that with an ORDER BY clause and a LIMIT (TOP if you're using Microsoft SQL Server) like so:

SELECT Employees.LastName
FROM Employees
INNER JOIN Orders -- Returns a row for every Order for each Employee
    ON Employees.EmployeeId = Orders.EmployeeId
INNER JOIN OrderDetails -- Returns a row for every OrderDetail of every Order. 
    ON Orders.OrderID = OrderDetails.OrderID
ORDER BY OrderDetails.Quantity DESC
LIMIT 1;

The final note here is more of a question on what your OrderDetails table represents (as a few other users have hinted at)? Is there always only one row in OrderDetails per Order like your example data shows or can have an Order relate to multiple rows in the OrderDetails table?

If an Order can relate to multiple OrderDetails then you need to do one more operation called a GROUP BY which will allow us to condense multiple OrderDetail rows that belong to the same Order per Employee and then aggregate their Quantities by summing them like so:

SELECT Employees.LastName
FROM Employees
INNER JOIN Orders -- Returns a row for every Order for each Employee
    ON Employees.EmployeeId = Orders.EmployeeId
INNER JOIN OrderDetails -- Returns a row for every OrderDetail of every Order. 
    ON Orders.OrderID = OrderDetails.OrderID
GROUP BY Employees.LastName, Orders.OrderID -- This condense the rows back down to just one row per Order per Employee. 
ORDER BY SUM(OrderDetails.Quantity) DESC
LIMIT 1;

Note the above gets you the LastName of the Employee who had the largest Order Quantity on a single Order. For example if EmployeeA has an Order with Quantity = 10 and EmployeeB has two different Orders, one with Quantity = 7 and the other with Quantity = 5, the above query will get you EmployeeA's LastName. But if you want to condense all Quantities between different Orders for the same Employee into one row to compare, such that in my previous example you'd want EmployeeB (because the sum of his two Orders' Quantities = 12 which is greater than 10 in EmployeeA's only Order) then you can loosen the GROUP BY clause to group on just the Employee.LastName field like so:

SELECT Employees.LastName
FROM Employees
INNER JOIN Orders -- Returns a row for every Order for each Employee
    ON Employees.EmployeeId = Orders.EmployeeId
INNER JOIN OrderDetails -- Returns a row for every OrderDetail of every Order. 
    ON Orders.OrderID = OrderDetails.OrderID
GROUP BY Employees.LastName -- This condense the rows down to just one row per Employee's LastName. 
ORDER BY SUM(OrderDetails.Quantity) DESC
LIMIT 1;

---

Now let's use what we learned above to answer your second question in the comments on "What product was ordered the most by customers in Germany?":

-- Gets the Product with the highest Quantity on a single OrderDetail
SELECT Products.ProductID, Products.ProductName -- The ID and Name of the Product
FROM Products -- Starting with what we want
INNER JOIN OrderDetails -- Getting the OrderDetails for each Product that was ordered
    ON Products.ProductID = OrderDetails.OrderID
INNER JOIN Orders -- Get the Order information for each OrderDetails so we can get the CustomerID
    ON OrderDetails.OrderID = Orders.OrderID
INNER JOIN Customers -- Get the Customers so we can filter on only Customers from Germany
    ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.Country = 'Germany' -- Filter out all records that don't have a Country = Germany
ORDER BY OrderDetails.Quantity DESC -- Order the rows by largest quantity first
LIMIT 1; -- Only return the top record by highest Quantity

Or:

-- Gets the Product with the highest Quantity on a single Order
SELECT Products.ProductID, Products.ProductName -- The ID and Name of the Product
FROM Products -- Starting with what we want
INNER JOIN OrderDetails -- Getting the OrderDetails for each Product that was ordered
    ON Products.ProductID = OrderDetails.OrderID
INNER JOIN Orders -- Get the Order information for each OrderDetails so we can get the CustomerID
    ON OrderDetails.OrderID = Orders.OrderID
INNER JOIN Customers -- Get the Customers so we can filter on only Customers from Germany
    ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.Country = 'Germany' -- Filter out all records that don't have a Country = Germany
GROUP BY Products.ProductID, Products.ProductName, Orders.OrderID -- Condenses all the OrderDetails rows to a single Order per Product and sums the Quantities for a total per Product per Order
ORDER BY SUM(OrderDetails.Quantity) DESC -- Order the rows by largest quantity first
LIMIT 1; -- Only return the top record by highest Quantity

Or:

-- Gets the Product with the highest Quantity across all Orders
SELECT Products.ProductID, Products.ProductName -- The ID and Name of the Product
FROM Products -- Starting with what we want
INNER JOIN OrderDetails -- Getting the OrderDetails for each Product that was ordered
    ON Products.ProductID = OrderDetails.OrderID
INNER JOIN Orders -- Get the Order information for each OrderDetails so we can get the CustomerID
    ON OrderDetails.OrderID = Orders.OrderID
INNER JOIN Customers -- Get the Customers so we can filter on only Customers from Germany
    ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.Country = 'Germany' -- Filter out all records that don't have a Country = Germany
GROUP BY Products.ProductID, Products.ProductName -- Condenses all the OrderDetails and Orders rows to a single row per Product and sums the Quantities for a total per Product
ORDER BY SUM(OrderDetails.Quantity) DESC -- Order the rows by largest quantity first
LIMIT 1; -- Only return the top record by highest Quantity