Databases Querying Relational Databases Subqueries Subqueries

Kareem Jeiroudi
Kareem Jeiroudi
14,982 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,018 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;