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

Databases

Jan Svensson
Jan Svensson
10,005 Points
Posted by Jan Svensson
Jan Svensson
10,005 Points

Joining Tables With SubQueries

The Following Question is :

"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 seem to be getting the exact result that I want with my code but it doesn't pass. Can anybody see what is wrong?

3 Answers

Chris Jones
seal-mask
.a{fill-rule:evenodd;}techdegree seal-36
Chris Jones
Java Web Development Techdegree Graduate 23,933 Points
Chris Jones
.a{fill-rule:evenodd;}techdegree seal-36
Chris Jones
Java Web Development Techdegree Graduate 23,933 Points

Hey Jan, which code challenge is this? Without knowing that I can only guess as to what the SQL would be:

SELECT *
FROM "Sale"
WHERE "CustomerID" IN (SELECT "CustomerID" FROM "Customer" WHERE "Gender" = 'F')
Jan Svensson
Jan Svensson
10,005 Points
Jan Svensson
Jan Svensson
10,005 Points

Hey Chris, It is the very last one in "Querying Relational Databases".

My query was something like you posted. I've commented mine in the comment above.

Chris Jones
seal-mask
.a{fill-rule:evenodd;}techdegree seal-36
Chris Jones
Java Web Development Techdegree Graduate 23,933 Points
Chris Jones
.a{fill-rule:evenodd;}techdegree seal-36
Chris Jones
Java Web Development Techdegree Graduate 23,933 Points

Thanks, Jan. I think you're query works, but I think the code challenge just wants you to use the IN keyword.

Kudos for coming up with your own solution, though, and thinking outside the box:). Nothing wrong with that. I think the IN keyword is just more conventional.

Jan Svensson
Jan Svensson
10,005 Points
Jan Svensson
Jan Svensson
10,005 Points

The IN keyword was used in the assignment right before this one, but now they want to do it with a 'derived table'. But I agree, the IN keyword solution seems much more conventional in this case :)

Chris Jones
seal-mask
.a{fill-rule:evenodd;}techdegree seal-36
Chris Jones
Java Web Development Techdegree Graduate 23,933 Points
Chris Jones
.a{fill-rule:evenodd;}techdegree seal-36
Chris Jones
Java Web Development Techdegree Graduate 23,933 Points

Oh, sorry about that.

This query passed:

SELECT *
FROM sale AS s
INNER JOIN (SELECT customerid FROM customer WHERE gender = 'F') AS c
ON s.customerid = c.customerid;

I'm not sure why it wouldn't take your query. I just watched the "Using a Subquery to create a temporary table (part 1) to see exactly what they did and copied that. Maybe the code challenge wanted a very specific syntax? I wonder if you naming the derived table "Customer" caused confusion because there is already a "Customer" table that you're basing the derived table on.

Let me know if you have any more questions!

Jan Svensson
Jan Svensson
10,005 Points
Jan Svensson
Jan Svensson
10,005 Points

Thanks a lot Chris! Yes, it seems like it could have been the naming that caused the problem.

Ken Alger
STAFF
Ken Alger
Treehouse Teacher
Ken Alger
Ken Alger
Treehouse Teacher

Jan;

Can you post the query you are using?

Thanks,
Ken

Jan Svensson
Jan Svensson
10,005 Points
Jan Svensson
Jan Svensson
10,005 Points

Certainly!

SELECT Sale.* FROM Sale INNER JOIN (SELECT * FROM Customer WHERE Gender = 'F') AS Customer ON Sale.CustomerID = Customer.CustomerID

Brian Vancil
Brian Vancil
3,301 Points
Brian Vancil
Brian Vancil
3,301 Points

I tried the following query, which it also didn't like:

SELECT s.* FROM Sale AS s INNER JOIN (SELECT CustomerID FROM Customer WHERE Gender = 'F') AS c ON s.CustomerID = c.CustomerID;
Chris Jones
seal-mask
.a{fill-rule:evenodd;}techdegree seal-36
Chris Jones
Java Web Development Techdegree Graduate 23,933 Points
Chris Jones
.a{fill-rule:evenodd;}techdegree seal-36
Chris Jones
Java Web Development Techdegree Graduate 23,933 Points

Hey Brian, did you try your query with all lower case letters for table and column names? I wonder if you have to use quotations when mixing capitalized letters in table and column names.