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.
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
GROUP BY ShipperName;
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
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.