CODEWITHSUNDEEP
sql
Steve Ahlswede
Steve Ahlswede

Reputation: 621

Better way to join multiple tables using SQL

I am messing around with the toy databases on W3School for learning SQL and I was trying to make a table that allowed me to find out the following information on a given OrderID:

I ended up achieving it through this code:

SELECT 
    Price * Quantity AS TotalCost, 
    ProductName, SupplierName, ShipperName 
FROM
    (SELECT * 
     FROM 
         (SELECT * 
          FROM 
              (SELECT * 
               FROM Products
               LEFT JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID) AS Products
          LEFT JOIN 
              OrderDetails ON Products.ProductID = OrderDetails.ProductID) AS OrderDetails
     LEFT JOIN 
         Orders ON OrderDetails.OrderID = Orders.OrderID) AS Orders
LEFT JOIN 
    Shippers ON Orders.ShipperID = Shippers.ShipperID 
WHERE 
    Orders.OrderDetails.OrderID = 10248;

However this seems like a total mess and I assume there must be a better way of achieving the same result. I did look around at other threads to see if I could find other ways, and the threads seemed to suggest that these subqueries are not necessary and that I could just chain the JOINs together without using subqueries, but when I tried this I got errors from the W3School compiler. Not sure if this is a problem with the website itself or if the error was on my end. For example, if I try this I get an error:

SELECT 
    Price * Quantity AS TotalCost, 
    ProductName, SupplierName, ShipperName
FROM
    Products
LEFT JOIN 
    Suppliers ON Products.SupplierID = Suppliers.SupplierID
LEFT JOIN 
    OrderDetails ON Products.ProductID = OrderDetails.ProductID
LEFT JOIN 
    Orders ON OrderDetails.OrderID = Orders.OrderID
LEFT JOIN 
    Shippers ON Orders.ShipperID = Shippers.ShipperID 
WHERE 
    Orders.OrderDetails.OrderID = 10248;

Error:

Syntax error (missing operator) in query expression 'Products.SupplierID=Suppliers.SupplierID LEFT JOIN OrderDetails ON Products.ProductID=OrderDetails.ProductID LEFT JOIN Orders ON OrderDetails.OrderID=Orders.OrderID LEFT JOIN Shippers ON Orders.ShipperID=Shippers.ShipperID'.

Upvotes: 1

Views: 1308

Answers (3)

FlexYourData
FlexYourData

Reputation: 2344

The problem is probably with this:

WHERE Orders.OrderDetails.OrderID = 10248;

Unless the database is called "Orders" (is it?), You appear to be referring to two tables at once. Use either:

WHERE Orders.OrderID = 10248;

or

WHERE OrderDetails.OrderID = 10248;

EDIT:

Assuming you mean this page, you need to add parentheses and make the correction I mention above. I suppose they are requiring parentheses in this test so that it works in Access as well as others.

SELECT Price*Quantity AS TotalCost, ProductName, SupplierName, ShipperName
FROM ((((Products 
LEFT JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID)
LEFT JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID)
LEFT JOIN Orders ON OrderDetails.OrderID = Orders.OrderID)
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID) 
WHERE OrderDetails.OrderID = 10248;

Upvotes: 3

user18098820
user18098820

Reputation:

You're not to far off. Please try the following version tidied up with tables aliases:

SELECT Price*Quantity AS TotalCost, ProductName, SupplierName, ShipperName
FROM Products p
LEFT JOIN Suppliers s ON p.SupplierID = s.SupplierID
LEFT JOIN OrderDetails od ON p.ProductID = od.ProductID
LEFT JOIN Orders o ON od.OrderID = o.OrderID
LEFT JOIN Shippers sh ON o.ShipperID = sh.ShipperID 
WHERE o.OrderID = 10248;

The only thing that is really changed is the WHERE clause.

Upvotes: 1

Kat357
Kat357

Reputation: 21

You need to specify from which table those columns come from. You can't just say SELECT Price when you have multiple tables joined. For example, Price*Quantity would need to be something like Products.Price*Orders.Quantity

Upvotes: 2

Related Questions