Having a troubled time finishing this last Challenge 4 of 4.

Remember when using joins as subqueries that the inner statement belongs in the FROM of the outer statement, ie:

SELECT * FROM table AS t1
INNER JOIN (
  SELECT column FROM table2
  WHERE AnotherColumn = 'value'
) AS t2
ON t1.ForeignKey = t2.PrimaryKey;

Let's modify your answer to fit the structure above:

SELECT * FROM Sale /*Remove the WHERE from the outer statement*/
INNER JOIN (
  SELECT CustomerID FROM Customer
  WHERE Gender = "F" AS Gender ON Gender = "F");

You'll see that there are still issues with the aliasing of your subquery as well as applying the join ON the corresponding keys from each table. What you should do is close the parentheses after the WHERE of your inner statement and then apply the alias and join ON the CustomerID columns:

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;

Hopefully this helps you to understand where you were going wrong. If anything is still unclear, take another look at the subqueries Review and Practice video

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

1. Select all from sale table, represented by letter s for sale
2. inner join: combines the column values of two tables
3. select customer id from customer table where gender is female, represented by letter c for customer
4. ON: the relationship between the table. the tables both have a customer id
5. customer id from sales = customer id from customer

select * from Sale where CustomerID IN (select CustomerID from Customer where Gender = "F")

Hi Paul, I think I understand your question. The "t2" goes outside of the parenthesis because the closing paren is where the derived table definition stops. So you define the temp table inside the parenthesis and name it immediately after the closing.

I hope this helps.

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

SELECT * FROM Sale WHERE CustomerID IN (SELECT CustomerID FROM Customer WHERE Gender = "F" )