Geoffry Geoffry - 2 days ago 5
MySQL Question

Why does SQL Return too Many Results?

When I enter this query,

SELECT OrderDetails.OrderDetailID, Customers.CustomerName
FROM OrderDetails, Customers
NATURAL JOIN Products
NATURAL JOIN Customers
WHERE SupplierID = 5;


1001 records are returned.

When I enter this query,

SELECT OrderDetailID
FROM OrderDetails
NATURAL JOIN Products
WHERE SupplierID = 5;


11 records are returned

As you might expect there are 91 records in the Customer table and 11 results are returned in the second query and 91*11=1001. I don't know how to get rid of this however and to prevent duplicate results being returned in the first query such as this.

Duplicate Returns

Schema's attached: http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

Answer

You'll need to use the Orders table to join in Customers from the OrderDetails since OrderDetails doesn't contain the customerid that is needed for you to get to that customers table.

A better query would be:

    SELECT OrderDetails.OrderDetailID, Customers.CustomerName
    FROM OrderDetails
        INNER JOIN orders on OrderDetails.orderid = Orders.orderid
        INNER JOIN customers on Orders.customerID = Customers.customerID
        INNER JOIN products on OrderDetails.productID = Products.productID
    WHERE products.SupplierID = 5;

Here we avoid Natural Joins since those can be unpredictable and when writing SQL it's always a good idea to be as explicit as possible. Furthermore, we use the Orders table to get the link to customers.

Comments