Databases Querying Relational Databases Subqueries Subqueries

Erin DeSpain
Erin DeSpain
4,363 Points

Impossible Results? "The subquery didn't return all CustomerIDs for people who identify as female (F)."

Question Content:

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.


My Code:

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

Response:

Bummer: The subquery didn't return all CustomerIDs for people who identify as female (F).


Question:

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.

1 Answer

KRIS NIKOLAISEN
KRIS NIKOLAISEN
54,293 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
Erin DeSpain
Erin DeSpain
4,363 Points

Thank you! That was perfect!