I am doing practice queries to improve my SQL skills (I'm a beginner) and I ran into this problem that I need help with using the Northwind database. The requested query should:
Give the name of employees and the city where they live for employees who have sold to customers in the same city.
What I wrote for this was:
USE Northwind;
SELECT DISTINCT FirstName, LastName, e.City
FROM Employees e
INNER JOIN Orders o ON e.EmployeeID = O.EmployeeID
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN Customers ON c.City = e.City
WHERE e.City = ANY(SELECT Customers.City FROM Customers);
I am returned 6 employees but I am not sure that they are correct and I believe that my WHERE statement is the wrong one as well.
Basically my question is, am I using the correct join statements and how do I go about filtering results with the WHERE statement? I'm not exactly sure how to compare one specific record with other records. Coming from a Java background I am used to for-loops that can check each individual "object" (record) with a specific field from another "object". In this case I am wondering how I can check the City attribute of each record from the Employees table with the City attribute of the records on the Customers table. Any and all advice is appreciated, thanks!
I think you only need to join the customer table once and have both requirements (being on the same order and same city as the employee) as your join requisites e.g
SELECT DISTINCT FirstName, LastName, e.City
FROM Employees e
INNER JOIN Orders o ON e.EmployeeID = O.EmployeeID
INNER JOIN Customers c ON o.CustomerID = c.CustomerID AND c.City = e.City
Alternatively you can just join the customer on the order id and filter the city requirement in the where clause. Performance-wise there shouldn't be any difference however if you are going to look back on the script at some point it may help you to remember
SELECT DISTINCT FirstName, LastName, e.City
FROM Employees e
INNER JOIN Orders o ON e.EmployeeID = O.EmployeeID
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE e.City = C.City
I think the important part to get your head round is that after you have joined the table it is effectively one table with columns from both (or more with multiple joins). The inner join conditions will filter out rows that do not have a match between the two tables and then you are left to compare columns