How to Resolve SQL Error 1066: Not Unique Table/Alias?

Error 1066: Not Unique Table/Alias: 'user'

When encountering error 1066 in SQL, it indicates that a table or alias used in a query has been referenced multiple times without unique identifiers. In the provided code, the error arises from the "user" table being joined twice without aliases:

SELECT article.* , section.title, category.title, user.name, user.name
FROM article
INNER JOIN section ON article.section_id = section.id
INNER JOIN category ON article.category_id = category.id
INNER JOIN user ON article.author_id = user.id
LEFT JOIN user ON article.modified_by = user.id
WHERE article.id = '1'

To resolve the issue, the second reference to the "user" table should be assigned an alias. This differentiates the two instances and allows the database to distinguish between them:

SELECT article.* , section.title, category.title, user.name, u2.name
FROM article
INNER JOIN section ON article.section_id = section.id
INNER JOIN category ON article.category_id = category.id
INNER JOIN user ON article.author_id = user.id
LEFT JOIN user u2 ON article.modified_by = u2.id
WHERE article.id = '1'

In this modified code, the second reference to the "user" table is assigned the alias "u2." This allows the database to differentiate between the two instances and resolve the error.

The above is the detailed content of How to Resolve SQL Error 1066: Not Unique Table/Alias?. For more information, please follow other related articles on the PHP Chinese website!