Why does this SQL code give error 1066 (Not unique table/alias: 'user')?

Question

This is my table structure:

The error message is:

#1066 - Not unique table/alias: 'user'

The following is my code.

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'

Answer 1

You need to give the user table an alias the second time you join to it

e.g.

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'

Answer 2

You have mentioned "user" twice in your FROM clause. You must provide a table alias to at least one mention so each mention of user. can be pinned to one or the other instance:

...

FROM article INNER JOIN section ON article.section_id = section.id INNER JOIN category ON article.category_id = category.id INNER JOIN user AS user1 ON article.author_id = user1.id LEFT JOIN user AS user2 ON article.modified_by = user2.id WHERE article.id = '1'

(You may need something different - I guessed which user is which, but the SQL engine won't guess.)

Also, maybe you only needed one "user". Who knows?

Answer 3

Your error is because you have:

     JOIN user ON article.author_id = user.id
LEFT JOIN user ON article.modified_by = user.id

You have two instances of the same table, but the database can't determine which is which. To fix this, you need to use table aliases:

     JOIN USER u ON article.author_id = u.id
LEFT JOIN USER u2 ON article.modified_by = u2.id

It's good habit to always alias your tables, unless you like writing the full table name all the time when you don't have situations like these.

The next issues to address will be:

SELECT article.* , section.title, category.title, user.name, user.name

1) Never use SELECT * - always spell out the columns you want, even if it is the entire table. Read this SO Question to understand why.

2) You'll get ambiguous column errors relating to the user.name columns because again, the database can't tell which table instance to pull data from. Using table aliases fixes the issue:

SELECT article.* , section.title, category.title, u.name, u2.name