sql · level 2 · unit 3 · lesson 3
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
Add one JOIN for every extra table you need.
Customers and dishes have a link: one customer can order many dishes, and one dish can be ordered by many customers. The orders table sits in the middle, with a foreign key to each.
To see who ordered what, start from orders and add a JOIN for each table it points at. Every JOIN has its own ON.
Once all three are joined, you can do maths across them, like d.price * o.quantity for the cost of each order.
SELECT c.name, d.dish, o.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN dishes d ON o.dish_id = d.id
ORDER BY o.id;Forget an ON and every row pairs with every row of that table.
guess first
What did each customer spend?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT c.name, SUM(d.price * o.quantity) AS spent
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN dishes d ON o.dish_id = d.id
GROUP BY c.name
ORDER BY spent DESC;your turn
Your turn. Who ordered a dish?
Type a dish between the quotes: Toastie, Soup, Pancakes or Brownie. You get everyone who ordered it, and how many.
result
Your answer appears here as a table.
fix it
Leo ordered two dishes, not ten. Fix it!
result
Your answer appears here as a table.
- Count the joins, then count the
ONparts. - With no
ON, each of Leo's orders pairs with every dish on the menu. - Add ON o.dish_id = d.id after JOIN dishes d, then press run.
nailed it
you joined three tables at once. :D
- A many-to-many link needs a table in the middle.
- Add one
JOIN, with its ownON, for each table. - Maths can mix columns from every joined table.
new words in your collection
- many-to-many
This lesson isn’t saved. A free account remembers every lesson you finish.