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

User · Answer

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'

User · Answer

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

User · Answer

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

User · Answer

SELECT art     sec section title  cat title  use1 name  use2 name as modifiedby FROM article art INNER JOIN section sec ON art section id   sec section id INNER JOIN category cat ON art category id   cat id INNER JOIN user use1 ON art author id   use1 id LEFT JOIN user use2 ON art modified by   use2 id WHERE art id    1     Hope This Might Help

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

Examples related to sql

Examples related to mysql

Examples related to join

Examples related to mysql-error-1066