I'm not new to SQL coding, I'm just typically used to doing small data requests in PHP from the database but every now and then am faced with an issue to where I need to join tables and it's just way over my head. I decided I would start expanding my knowledge in that area by trying to accomplish one single task, but am totally stumped.
I'm learning this from W3Schools Website so all my referencing is from the DATABASE in their TryItEditor located here:
https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all
I'm trying to use SQL to display the CustomerName with the OrderID they placed, as well as the ProductName and ProductID.
The way I was trying to do this-- which I know is probably wrong, but I'm new to this-- was to use a LEFT JOIN or INNER JOIN to first JOIN the the two columns Products.ProductID and OrderDetails.ProductID. Then I was trying to JOIN OrderDetails.ProductID as well. Then finally, trying to JOIN Customers.CustomerID to Orders.CustomerID.
Here is my Code:
SELECT Products.ProductID, OrderDetails.ProductID, Products.ProductName, Customers.CustomerName, Products.ProductName, Orders.OrderID FROM OrderDetails LEFT JOIN Products ON OrderDetails.ProductID = Products.ProductID LEFT JOIN Orders ON OrderDetails.ProductID = Products.ProductID LEFT JOIN Customers ON Customers.CustomerID = Orders.CustomerID;
However when I ran this code, it is displaying 101528 rows though I clearly know according to the Orders table that there has only been a total of 196 Orders.
I've tried going through various tutorials I found online to understand how to do this, but I'm not getting the result I desire. If someone could point me in the right direction what I'm trying to do, It would be greatly appreciated! Thanks!
UPDATE: My desired output is to see 196 Rows (Number of Orders) with the following Columns:
Product ID -- ProductName -- CustomerName -- OrderID --
UPDATE to Nathans Answer:
SELECT Customers.CustomerID, Orders.OrderID, Products.ProductID, Products.ProductName, Customers.CustomerName FROM Orders INNER JOIN OrderDetails ON OrderDetails.OrderID = Orders.OrderID INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
I was able to get the desired view, but still too many rows.
Start with inner joins. Left join means you show the row on the left even if the row on the right isn’t there. Time for left joins after you can do inner joins.
It helps to have an ERD. If you don’t have one already, draw one. Pick a starting table that looks like a good place to get all the information you need. The joins follow the relationships in the diagram.
I would first join order to order detail using order Id. Then join order detail to product using product Id. Then join order to customer by customer Id.
Add the joins one at a time and confirm the results make sense. Notice that if you join orders to order details by order Id, you are going to get back a number of rows equal to the number of details, not the number of orders.