How to Resolve MySQL Error 1066: Duplicated Table/Alias 'user'?

Error 1066: Duplicated Table/Alias 'User'

When executing an SQL query, you may encounter error 1066 (Not unique table/alias: 'user'). This error indicates that the 'user' table in your query is referenced multiple times without distinct aliases.

Problem:

Consider the following table structure:

Article       Section      Category     User
id | title   id | title    id | title    id | name
------------ ------------ ------------ ------------
1  | Article 1 10 | Section 1 100 | Category 1 1   | Author

And this SQL query:

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'

When executing this query, you will get the error 1066 because the 'user' table is joined twice without specifying different aliases.

Solution:

To resolve this issue, assign a unique alias to the second instance of the 'user' table, like 'u2':

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'

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