Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
Tadjiev Codes
9,626 PointsSQL INNER JOIN misunderstandings
Dear Treehouse folks, Hope everyone is doing great) Quick question here... This is the question that I have to implement in SQL> List the customer’s name, product name, retail price and discount amount (see aliases below) for all items on order 486 that have been discounted (where quoted price is less than retail price)?
SELECT DISTINCT c.firstname AS "FirstName", c.lastname AS "LastName", p.name AS "Product", p.retailprice AS "RetailPrice"
FROM customers c INNER JOIN products p
ON p.id = c.id
INNER JOIN order_details od
ON od.quotedprice < p.retailprice
AND od.orderid = 486;
SELECT DISTINCT c.firstname AS "FirstName", c.lastname AS "LastName", p.name AS "Product", p.retailprice AS "RetailPrice"
FROM customers c INNER JOIN products p
ON p.productid = od.productid
INNER JOIN order_details od
ON od.quotedprice < p.retailprice
AND od.orderid = 486;
SELECT DISTINCT c.firstname AS "FirstName", c.lastname AS "LastName", p.name AS "Product", p.retailprice AS "RetailPrice"
FROM customers c INNER JOIN order_details od
ON od.orderid = 486
INNER JOIN products p
ON od.quotedprice < p.retailprice;
-- Kind of correct VERSION
-- But how to get only 2 display items for order 486 and how to display the correct discounted price???
SELECT DISTINCT c.firstname AS "FirstName", c.lastname AS "LastName", p.name AS "Product", p.retailprice AS "RetailPrice", od.quotedprice AS "Discounted Price"
FROM customers c INNER JOIN order_details od
ON od.orderid = 486
INNER JOIN products p
ON od.quotedprice < p.retailprice;
I have tried to do it with many more versions than this but seems that I'm really stuck with this. It should show the result of
ON od.quotedprice < p.retailprice; --As an alias Discounted Price as the last column name
And it should return only 2 rows of data. Where I can upload my database to show which one I'm working with? Currently, I'm using https://apex.oracle.com/pls/apex/f?p=4500:1003:119228238820708::::: But I'm not sure that I can share it with someone and Treehouse workspaces don't seem to work for that purpose? This is the workspace I created just to upload the sql.text file of the database I have : https://w.trhou.se/x5mecm6ry3 Thanks in advance)
3 Answers
Steven Parker
231,248 PointsIt looks like you're confusing the join criteria (ON) with the filter criteria (WHERE). The columns you JOIN ON should establish the relationship between the tables. This will often be a foreign key constraint in one of the tables. For example:
INNER JOIN products p ON od.ProductId = p.Id
And it looks like you will need to join the Orders table with these other three to establish all the relationships.
Then anything that limits the rows shown, like the order ID and price difference, should be part of a WHERE clause.
And yes, once you get that straightened out, you'll get two rows (for a Helmet and a Bag).
Tadjiev Codes
9,626 PointsSELECT c.firstName, c.lastName, p.Name, p.RetailPrice, p.RetailPrice - od.QuotedPrice AS "Discounted Amt."
FROM Customers c
INNER JOIN Orders o
ON c.Id = o.CustomerId AND o.Id = 486
INNER JOIN Order_Details od
ON o.Id = od.OrderId
INNER JOIN Products p
ON od.ProductId = p.Id AND QuotedPrice < p.RetailPrice;
Its so confusing for me to find foreign keys now. As I got used to MS ACCESS and there it was clear whats a foreign key and primary. Thanks anyways Mr.Steven)))
Steven Parker
231,248 PointsThe foreign keys are not hard to spot. For example, on line 93 of LabsSQL.txt you see this:
CONSTRAINT Order_Details_Product_FK FOREIGN KEY (ProductId) REFERENCES Products(Id)
Tadjiev Codes
9,626 PointsAlright thanks) Now I know better how to find them
Tadjiev Codes
9,626 PointsTadjiev Codes
9,626 PointsI can't seem to understand what they might share exactly the customers and products. Probably they don't share ids but what do they share though? That's only with the second query where I'm doing
ON p.productid = od.productid