Problem
Task
Return each customer email once for customers who ordered at least one product in the Gizmo category.
Schema
Table Schema
customers(id, name, email)
products(id, title, category)
orders(id, customer_id, product_id, ordered_at)Input
Sample Data
customers
| id | name | |
|---|---|---|
| 1 | Alice | alice@example.com |
| 2 | Bob | bob@example.com |
products
| id | title | category |
|---|---|---|
| 10 | Mini Gizmo | Gizmo |
| 11 | Desk Mat | Office |
orders
| id | customer_id | product_id | ordered_at |
|---|---|---|---|
| 401 | 1 | 10 | 2026-04-01 |
| 402 | 1 | 10 | 2026-04-05 |
| 403 | 2 | 11 | 2026-04-07 |
Output
Expected Output
| alice@example.com |
Answer
Check Your Solution
Show Answer and Explanation
Correct Answer
SELECT DISTINCT c.email
FROM orders AS o
INNER JOIN customers AS c
ON o.customer_id = c.id
INNER JOIN products AS p
ON o.product_id = p.id
WHERE p.category = 'Gizmo';Explanation
The orders table connects customers and products. DISTINCT is needed because the same customer can order more than one Gizmo product.
Common Mistakes
- Joining customers directly to products without using orders.
- Forgetting DISTINCT and returning duplicate customer emails.
- Filtering c.category even though category belongs to products.
Concepts
Related Concepts
INNER JOIN
DISTINCT
Foreign Keys
JOIN
Three Tables
Next practice