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

Posted by David Wright
David Wright
4,437 Points

Last Task of Querying Relational Databases: Subquery Challenge 4 of 4

Please see attached code: if it doesn't show, I did this:

SELECT SaleID, CarID, Sale.CustomerID AS CustomerID, LocationID, SalesRepID, SaleAmount, SaleDate FROM Sale AS s INNER JOIN (SELECT Customer.CustomerID FROM Customer WHERE customer.Gender = 'F') AS f ON Sale.CustomerID = f.CustomerID;

I don't understand why my answer is failing. Thank you for your help in advance!

1 Answer

Jonathan Grieve
MOD
Jonathan Grieve
Treehouse Moderator 91,253 Points
Jonathan Grieve
Jonathan Grieve
Treehouse Moderator 91,253 Points

Hi David!

Let's have a look at this then.

This is your code I put together in markdown. (See the Markdown cheatsheet for more on this.)

SELECT SaleID, CarID, Sale.CustomerID AS CustomerID, LocationID, SalesRepID, SaleAmount, SaleDate 
FROM Sale AS s INNER JOIN (
   SELECT Customer.CustomerID FROM Customer 
   WHERE customer.Gender = 'F') AS f ON Sale.CustomerID = f.CustomerID;

And this is the code I used that passed the challenge.

SELECT * FROM Sale AS s /* Let's alias the Sale table as 's' so it can be easily referred to later on */
INNER JOIN (
  SELECT CustomerID FROM Customer
  WHERE Gender = 'F'
) AS c /* Aliased derived Customer table as 'c' */
ON s.CustomerID = c.CustomerID;

There are 2 main points here.

You don't need to select all the specific columns for this challenge. You can get away with using the universal selector.. I'm not sure if that, on it's own is what is causing the challenge to fail but it is good to know. :-)

Secondly you're trying to use real column names as Aliases. What you need to to is Alias the whole Sale table and then we can refer to that later onwhen we do the INNER JOIN as deriative table. This is the main difference between this task as the previous 3.

You then alias the sub query the same way you did the main query and then you reference these Aliases when you do the join.

ON s.CustomerID, c.CustomerID.

Hope this helps :)

David Wright
David Wright
4,437 Points

Thank you, Jonathan!

I did a few variations of this query (and derived table subquery) in the workspaces playground as well as in the challenge. In those variations I tried SELECT * rather than writing them out, I also tried Aliasing Sale AS s as well as refraining from aliasing s.CustomerID AS CustomerID. I'm not sure what was going on; in the workspaces my query was generating the exact same output as your code. However, in the challenge my code was generating a "Incorrect Response Prompt", meanwhile yours worked.

Nevertheless, thank you so much for your assistance and I appreciated the stylistic advice!

Jonathan Grieve
Jonathan Grieve
Treehouse Moderator 91,253 Points
Jonathan Grieve
Jonathan Grieve
Treehouse Moderator 91,253 Points

No worries glad it helped :)

Unfortunately these code engines can be very specific about the code it expects. That's what drove me to make my point about the universal selector. What strikes me about this course is the simplicity of the syntax. It is far less scary than I thought it would be to query multiple tables and write sub queries