Erin DeSpain3,516 Points
Impossible Results? "The subquery didn't return all CustomerIDs for people who identify as female (F)."
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.
SELECT * FROM Sale AS s INNER JOIN ( SELECT Gender FROM Customer WHERE Gender = 'F') AS c ON c.CustomerID = s.CustomerID
Bummer: The subquery didn't return all CustomerIDs for people who identify as female (F).
Where did things go wrong? NOTE: I've also tried entering the
Sale Fields individually to prevent the normal duplication of fields one gets with a join. Consequence: same exact result.
KRIS NIKOLAISEN53,537 Points
Since you are joining CustomerID you will want to select CustomerID instead of Gender in your subquery.
SELECT * FROM Sale AS s INNER JOIN ( SELECT CustomerID FROM Customer WHERE Gender = 'F') AS c ON c.CustomerID = s.CustomerID