ambiguous column nameWhat is "ambiguous column name"?
SQLite stops with "ambiguous column name" when two joined tables share a column name and the query does not say which one it means.
What SQLite is telling you
ambiguous column name: nameThe query joins two tables that both have a column called name. In the shop data, customers.name is a person and products.name is a thing. When the query just says name, SQLite cannot know which one you mean, and it refuses to guess.
It happens most with id and name, because nearly every table has them. It only shows up once you join: the same query on one table works fine.
How to find it
The message names the column. Find every place the query uses it bare: in SELECT, WHERE, ORDER BY, GROUP BY and ON. Put the table (or its short alias) in front of each one: c.name, p.name. Even where only one table has the column, writing the prefix makes the query easier to read. If two prefixed columns would both come out called name, rename them with AS so the result is clear.
Run it: two tables with an id
Press Run and read the error from the bottom up.
Tables customers, products, orders, order_items
| id | name | year_group |
|---|---|---|
| 1 | Amara | 12 |
| 2 | Ben | 13 |
| 3 | Chloe | 12 |
| 4 | Dev | 12 |
| 5 | Ella | 13 |
| 6 | Femi | 13 |
| 7 | Grace | 10 |
| 8 | Hugo | 10 |
| id | name | category | price | stock |
|---|---|---|---|---|
| 1 | Sketchbook A4 | art | 4.5 | 30 |
| 2 | Acrylic paints | art | 8.0 | 12 |
| 3 | Brush set | art | 5.25 | 20 |
| 4 | Fineliners | art | 3.75 | 45 |
| 5 | Graph paper | maths | 2.0 | 60 |
| 6 | Scientific calculator | maths | 12.99 | 8 |
| 7 | Protractor | maths | 0.99 | 70 |
| 8 | Lab goggles | science | 6.5 | 15 |
| 9 | Periodic table poster | science | 3.0 | 25 |
| 10 | Hoodie | merch | 24.0 | 10 |
| 11 | Water bottle | merch | 9.5 | 18 |
| 12 | Lanyard | merch | 2.5 | 40 |
| id | customer_id | placed_on | status |
|---|---|---|---|
| 1 | 1 | 2026-09-16 | paid |
| 2 | 1 | 2026-09-03 | paid |
| 3 | 1 | 2026-09-21 | paid |
| 4 | 1 | 2026-09-03 | paid |
| 5 | 1 | 2026-09-20 | paid |
| 6 | 1 | 2026-09-27 | paid |
| 7 | 1 | 2026-09-16 | paid |
| 8 | 1 | 2026-09-09 | paid |
| 9 | 1 | 2026-09-08 | paid |
| 10 | 2 | 2026-09-28 | paid |
| 11 | 2 | 2026-09-30 | refunded |
| 12 | 2 | 2026-09-13 | paid |
First 12 of 41 rows. Query the table to see the rest.
| id | order_id | product_id | qty |
|---|---|---|---|
| 1 | 1 | 2 | 2 |
| 2 | 2 | 10 | 2 |
| 3 | 3 | 10 | 1 |
| 4 | 4 | 1 | 1 |
| 5 | 5 | 8 | 2 |
| 6 | 6 | 9 | 1 |
| 7 | 7 | 11 | 1 |
| 8 | 8 | 9 | 1 |
| 9 | 8 | 12 | 2 |
| 10 | 9 | 1 | 1 |
| 11 | 9 | 10 | 1 |
| 12 | 10 | 7 | 1 |
First 12 of 53 rows. Query the table to see the rest.
SELECT id, name, price, qty FROM products JOIN order_items ON order_items.product_id = products.id WHERE category = 'art';
Who bought the art supplies
The art teacher wants to know who bought art products: the customer's name as customer, the product's name as product, and the qty. It stops with "ambiguous column name". Fix it.
SELECT name AS customer, name AS product, qty FROM order_items AS oi JOIN orders AS o ON o.id = oi.order_id JOIN customers AS c ON c.id = o.customer_id JOIN products AS p ON p.id = oi.product_id WHERE category = 'art';