SilenceIsGolden SilenceIsGolden - 5 months ago 12
SQL Question

Getting info through 3 tables

I'm following the SQL tutorial from w3schools.

I want to get the value of all orders delivered by a shipper. I don't have any idea about how I can get these details as the info are in different tables and the INNER JOIN didn't worked for me.

Database: http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_groupby
By now, I managed to get the number of orders by each shipper.

SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;


How could I get the value of those?

Answer

To bring the Price of a Product into your query you will need to join in tables OrderDetails to the Order table on the Orders.Id and then join in the Products table to the OrderDetail table on ProductID

SELECT Shippers.ShipperName,
    COUNT(Orders.OrderID) AS NumberOfOrders,
    Sum(Products.price * OrderDetails.Quantity) AS SumOfPrice
FROM Orders
    LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
    LEFT JOIN OrderDetails ON ORders.OrderID = OrderDetails.OrderID
    LEFT JOIN Products ON OrderDetails.ProductID = Products.ProductID
GROUP BY ShipperName;

I just stuck with LEFT JOIN here as your example used, but an INNER JOIN would work just as well and be more efficient.

The FROM clause of the SQL statement is one of the first parts of the SQL to run against your database. It establishes which tables we are grabbing information from and the relationship between those tables (using the ON keyword). So here we bring in 4 tables, and use the ON keyword to show the relationship between all of them using their respective IDs. Then we can add their fields to the SELECT portion of the SQL statement and aggregate where needed.