CODEWITHSUNDEEP
mysqlsqlselect
RoccoDen91
RoccoDen91

Reputation: 185

How to solve the ambiguity error 1052 in mySql SELECT?

I'm making a mySql SELECT:

SELECT name, description, brand, Providers.name, Categories.name, sellingPrice, quantity
FROM Products 
INNER JOIN Providers ON Products.idProvider = Providers.id
INNER JOIN Categories ON Products.idCategory = Categories.id
WHERE category = 'tools';

but I become this error:

Error Code: 1052. Column 'name' in field list is ambiguous

even if I specified 'Table.column' (Providers.name, Categories.name).

Some help please ??

Upvotes: 0

Views: 604

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Simple rule: Qualify all column names when you write a query. Always.

If you give tables reasonable aliases, this is easy. So your query should look something like this:

SELECT p.name, p.description, p.brand, pr.name, c.name, p.sellingPrice, p.quantity
FROM Products p JOIN
     Providers pr
     ON p.idProvider = pr.id JOIN
     Categories c
     ON p.idCategory = c.id
WHERE c.category = 'tools';

I am guessing what tables the columns are coming from, so the qualified names may not be correct (your question doesn't provide this information).

Upvotes: 0

Ankit Das
Ankit Das

Reputation: 650

Did you try removing the name field in the beginning? Try this code

SELECT description, brand, Providers.name, Categories.name, sellingPrice, quantity
FROM Products 
INNER JOIN Providers ON Products.idProvider = Providers.id
INNER JOIN Categories ON Products.idCategory = Categories.id
WHERE category = 'tools';

Similarly provide proper Table.Column mapping in the select. If you have a name field in Products table, include Products.name in select

Whenever you join tables , you may find same column names in multiple tables. The SQL Engine doesnt know which to pick.So in order to differentiate you need to specify proper mappings. You can also specify simple Alias names instead of full Table names.

Upvotes: 1

Related Questions