SQL Comparison query

Question

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!

Your joins are not correct, as you're joining against the Customers table twice. Change `INNER JOIN Customers ON` to `AND` and get rid of the WHERE clause and you should be good. — Welbog, Jun 07 '17 at 13:54

MarkD · Accepted Answer · 2017-06-07T14:02:06.773

2

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

edited Jun 07 '17 at 14:02

answered Jun 07 '17 at 13:56

MarkD

1,043
12
18

I did not know that you could use an AND statement for joins, so thanks for teaching me that! Also, how I am interpreting this is that I don't think it would be logically correct because the requested query is for an Employee that has actually sold to a specific Customer has the same City value as them. Does the INNER JOIN on the EmployeeID have this covered? – Matt Grace Jun 07 '17 at 14:02
yes this works for that case. When you join two tables with an inner join you are effectively creating a new bigger table with columns from both tables your on clause will filter out. I could add some sample tables and outputs to the answer if it helps you visualize? – MarkD Jun 07 '17 at 14:04
Ok I understand. I understand Joins at a basic level I just wasn't sure if in this case it would actually filter results for what I needed in my query. Thanks for the help! – Matt Grace Jun 07 '17 at 14:08

SQL Comparison query

1 Answers1