Will my SQL query retrieve the desired information?

Question

I'm practicing writing SQL queries, I just wanted to check that my answer would give me the desired result.

I'm given a few tables of a database, I need to retrieve "the customer IDs of customers that have ordered any product from a supplier based in Canberra"

The tables:

• Suppliers (SupplierID, CompanyName, ContactName,ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, HomePage)
• Products (ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
• OrderDetails(OrderID, ProductID, UnitPrice, Quantity, Discount)
• Orders (OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
• Customers (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)
• Employees (EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath)
• Shippers (ShipperID, CompanyName, Phone)

The SQL query I've written:

SELECT Customer.CustomerID
FROM Customers, Orders
WHERE (Customers.CustomerID = Orders.CustomerID)
  AND (Orders.ProductID IN (SELECT Products.ProductID
                            FROM Suppliers, Products
                            WHERE (Products.SupplierID = Suppliers.SupplierID)
  AND (Suppliers.City = 'Canberra'))

The query should retrieve a list of customer numbers that match customers that have ever ordered a product that a Canberra supplier has made.

Answer 1

Do it with proper joins and aliases for the tables:

SELECT DISTINCT c.CustomerID
FROM Customers AS c 
INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID
INNER JOIN Products AS p ON o.ProductID = p.ProductID 
INNER JOIN Suppliers AS s ON p.SupplierID = s.SupplierID
WHERE s.City = 'Canberra'

Answer 2

I would change the SELECT to SELECT DISTINCT : otherwise you will get a CustomerID row for every order that matches the products condition; which can be multiple (orders) per customer.

While I would write the query exactly as you did (with the added DISTINCT), you might consider the following example to be more readable:

SELECT
    Customer.CustomerID
FROM
    Customers, 
    Orders,
    Products,
    Suppliers
WHERE
    Customers.CustomerID = Orders.CustomerID AND
    Orders.ProductID = Products.ProductID AND
    Products.SupplierID = Suppliers.SupplierID AND
    Suppliers.City = 'Canberra'
GROUP BY 
    Customer.CustomerID

Answer 3

That looks fine. If you just want the number of customers, you could select only the COUNT(Customer.CustomerID).