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 Querying Relational Databases Subqueries Subqueries

Kareem Jeiroudi
Kareem Jeiroudi
14,984 Points
Posted by Kareem Jeiroudi
Kareem Jeiroudi
14,984 Points

Last Challenge Task 4 of 4 Bummer: Your query didn't return all columns from the Sale table for who's CustomerIDs .....

In the very final challenge there's the following task description:

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.

And here's my solution:

SELECT S.* FROM SALE S
INNER JOIN (
  SELECT * FROM Customer
  WHERE Gender = 'F'
) AS female_customers ON S.CustomerID = female_customers.CustomerID;

I checked it a hundred of times if there's anything wrong with the query syntactically, I tried different type of aliasing tables but also columns, Tried returning (selecting) different columns, but nothing helped, it kept saying:

Bummer: Your query didn't return all columns from the Sale table for who's CustomerIDs belong to people who identify as female!

And I don't get why! I checked other guys solutions online, according to those, my code should work and return the required query. I also read the task over 10 times to make sure that I understood the task correctly. I'm going crazy, please some help ? from anyone who solved the exercise or has an idea what could be wrong

1 Answer

Mustafa Başaran
Mustafa Başaran
28,046 Points
Mustafa Başaran
Mustafa Başaran
28,046 Points

Hi Kareem, You actually need ONLY ONE COLUMN from the subquery. and that is the CustomerID column filtered on rows where the gender is female.

Only after then, you can join the Sale table on the subquery's result to retrieve all the Sale data where the customer's gender is female.

So, you only need to change the subquery accordingly. Then it should work. OK?

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