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
alm
4,268 PointsBummer, but it works?
The Challenge: In a car database there is a Sale table with columns, SaleID, CarID, CustomerID, LocationID, SalesRepID, SaleAmount and SaleDate and a Customer table with columns, CustomerID, FirstName, LastName, Gender and SSN. Use a subquery as a derived table to show all sales to female ('F') customers. Select all columns from the Sale table only.
I entered the following
SELECT s.* FROM Sale AS s INNER JOIN Customer AS c ON s.customerID = c.customerID WHERE c.gender = 'F';
The output gives me the desired results (I checked by removing the where statement to make sure it's only giving me the Females) but I get a "Bummer! Subquery should be contained within parentheses." Where would I put the parentheses that won't give me an SQL error.
Thanks!
3 Answers
Dave StSomeWhere
19,870 PointsWhile your SQL statement might work, the challenge says "Use a subquery" (MySQL Subquery Doc).
Your solution uses joins.
Here's the sample from the doc - notice that the subquery is an operand in the WHERE clause and contained in parenthesis.
Sample: SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2); - hope this helps.
alm
4,268 PointsThanks so much for the fast reply. I was able to solve the challenge using the following: SELECT * FROM Sale AS s INNER JOIN (SELECT CustomerID FROM Customer WHERE Gender = 'F') AS cf ON s.CustomerID = cf.CustomerID ;
While it was acceptable, what still I feel was misleading, was the instructions of the challenge where it explicitly stated to only include the columns from the Sales table. By using the inner join (two select statements), the CustomerID from the Customer table will also return in the data set. Nevertheless, I'm a happy camper and can resume my course :). But if there's a different way I can do this and not repeat the CustomerID, please share.
Dave StSomeWhere
19,870 PointsGlad you can move on - but in the spirit of good discussion, I think you missed the main point of the challenge. One cool thing about a subquery in the WHERE clause is that you don't need to do a JOIN. Doing a subquery in the JOIN will work but you're doing an unnecessary JOIN. This is what I think is the expected statement should of looked like - SELECT * FROM Sale WHERE CustomerID = (SELECT CustomerID FROM Customer WHERE Gender = 'F'); - Please notice that the subquery will return a set of rows with just 1 column (the CustomerID) which is filtered by the Gender field. Very handy spiffy SQL concept to understand. Hope that wasn't too much yada, yada, yada.
alm
4,268 PointsAppreciate the additional input....and BTW, I love ya da da :)....always good stuff. However, when I didn't use alias or INNER JOIN, the challenge barked at me. Just to make sure, I went back to the challenge and tried your statement and it said "Bummer! You're missing the INNER JOIN keywords". A very testy challenge if I do say so. Once again, many thanks for the input. It did help me get the WHERE statement in the right place.