Sql ошибка 1066

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

Not unique table/alias is an SQL error message that shows up on the screen if you don’t have the correct SQL join statement. It happens in lots of applications, and this article has selected the top five to teach you why it happens and how to fix it.

Our teaching approach will show you how you can be a better SQL developer and use SQL applications like SQL servers and MySQL. To follow along, grab your computer, and let’s fix the unique table/alias error in your SQL code.

Why Do Your SQL Statements Have No Unique Table or Aliases?

Your SQL statements have no unique table or aliases because of the following:

• There is no alias in your SQL statement
• You joined a table to itself
• Two tables have the same name and lower_case_table_names=1
• Your code has duplicate aliases or models

– There Is No Alias in Your SQL Statement

Different tables in your database can have columns with the same name. When you’re joining these tables using a SELECT statement, a conflict can occur that leads to the “unique” error. For example, the following SQL join statement will lead to the following error message when you run it: error code: 1066. not unique table/alias:

This error occurred because the SELECT statement called “account_users” twice from the “tech_articles” table. Also, the following is a similar example that causes the “mysql update not unique table/alias” error. The same will happen if you don’t use table aliases in your Laravel controller, and it will also lead to the “not unique table/alias laravel” error.

– You Joined a Table to Itself

In SQL, you don’t join a table to itself because it can lead to errors like the MySQL not unique table/alias join error. This type of error arises as a result of typographical errors. For example, the following SQL joins the “Shares” table to itself using a “LEFT JOIN”.

A similar error will happen in the following PHP code for “CodeIgniter” and it leads to the not unique table/alias CodeIgniter error. A quick overview of the code shows that the sample code selects data from the “web_service” table and again joins it to the same table.

– Two Tables Have the Same Name and Lower_case_table_names=1

The “lower_case_table_names” controls the letter case of database tables and names. This allows you to have two tables with names like “Table1” and “table1” in your database. Now, when you attempt to dump the table using MySQL dump, it’ll cause the mysqldump: got error: 1066: not unique table/alias error.

– Your Code Has Duplicate Aliases or Models

Just like how a lack of aliases can lead to the “unique” error in SQL, the duplicates can lead to errors like the er_nonuniq_table not unique table/alias typeorm error. For example, in the following query builder code, we used “site_users” twice in the “left” join statement. Behind the scenes, the execution of this code will cause an error because SQL cannot differentiate between them.

Also, duplicate models in your code can lead to the not unique table/alias sequelize error. That’s what is going on in the following code. We used the same model, “AccountHolder” twice in the “include” property. Like the previous example, this will cause an error when your application runs the code.

How Your SQL Statements Can Have Unique Tables and Aliases?

Your SQL statements can have a unique table or aliases if you do any of the following:

• Use an alias when you’re joining tables
• Don’t join the same table
• Rename your tables before a database dump
• Assign unique aliases to models in TypeORM
• Assign aliases to associations or relationships

– Use an Alias When You’re Joining Tables

Always use an alias in your SQL SELECT statement to prevent the “unique” table or aliases SQL error. With an alias, you can assign temporary names to the tables, and you can prevent conflicts during database joins. The following is another version of the first SQL SELECT, this time, we’ve updated it to prevent an error.

For our second example, we used an alias the second time that we called “account_users” from the “tech_articles” table. By doing this, SQL can tell the tables apart and will not throw an error.

– Don’t Join the Same Table

The rule is to join columns from one table to columns in another table. Now, the following is a rewrite of a previous example that joined a table to itself using an SQL SELECT statement. Now, we’ve updated the code to prevent the error in the LEFT JOIN statement.

– Rename Your Tables Before a Database Dump

To prevent any errors about unique tables or aliases during a database dump, check your tables and rename similar table names. So, if you have table names like “Table1” and “table1” and “lower_case_table_names=1”, rename either of the tables to prevent a conflict.

You can use any of the following syntaxes to rename your database tables:

• ALTER TABLE name_of_old_table RENAME name_of_new_table;
• RENAME TABLE name_of_old_table TO name_of_new_table;
• RENAME TABLE name_of_old_table TO name_of_new_table, name_of_old_table TO name_of_new_table;

– Assign Unique Aliases to Models in Typeorm

If your TypeORM code shows an error about unique tables and aliases, it’s because you have duplicates. To fix this, use unique aliases in your SQL JOIN statements. The following is a rewrite of the previous code that used duplicate aliases in the SQL code. This time, the code has unique aliases that will prevent the error.

– Assign Aliases to Associations or Relationships

The use of aliases in your associations or models when using sequelize will prevent any error about unique tables and aliases. In the following, we’ve updated the code to use an alias. Afterward, we included the aliases and not the duplicates that caused the error in the first place.

Conclusion

This article discussed why your SQL statements would lead to an error about unique tables and aliases. Then in the second half of the article, we explained how to fix them, and the following is a quick summary of it all:

• A lack of aliases in your SQL statements will lead to an error about a unique table/alias.
• Joining a table itself will cause an error about unique tables and aliases.
• You can fix the “no unique table/alias” SQL error using aliases where it’s needed.
• Update your TypeORM code to use aliases, and you’ll prevent the unique table/alias error.
• Check your database tables for identical names before dumping the whole database.

What you’ve learned in this article will make you a good SQL developer. The rule is: to use aliases when needed and don’t duplicate them if you want to prevent an error.

Here is a starting point. V1, V2, V3 are aliases, if you even need them all.

You need to bring in tables before you reference them (not in the first select row, but in the joins after the select row).

In other words, you can reference them in the select column list. But you cannot reference them in joins if they are not brought in.

You can’t reference GEBEAUTE before you have brought it in

there are 2 ways of bringing it in

• the from XXX clause (that is your first table available brought in)
• the join XXX clause (that brings in tables 2 and beyond)

.

select VERANSTALTUNG.v_name, V_TYP.typ, RAUM.raumname, GEBEAUTE.gebeaute_name
From VERANSTALTUNG V1
JOIN VERANSTALTUNG V2 on V2. something
JOIN VERANSTALTUNG V3 on V3. something
JOIN RAUM on RAUM.gebeaute_id=GEBEAUTE.gebeaute_id -- error here still, see below

note you still haven’t brought in V_TYP or GEBEAUTE

it’s a mess. Less of a mess, but inching toward happiness.

Here is a simple illustration of it

line1:   select A.col1,B.col2,C.col3
line2:   from table1 A
line3:   join table2 B on B.blahblah=A.something
line4:   join table3 C on C.typ_id=D.month_id

It look good until line4. Because table D is not brought in yet.

I have two MySQL statemenents:

SELECT PRODUCTS.REFERENCE,PRODUCTS.NAME,PRODUCTS.PRICEBUY,PRODUCTS.PRICESELL,
       SUM(TICKETLINES.UNITS) AS UNITS,
       SUM(TICKETLINES.PRICE * TICKETLINES.UNITS) AS SUBTOTAL,
       SUM((TICKETLINES.PRICE * TICKETLINES.UNITS) * TAXES.RATE) As TAXES,
       SUM(TICKETLINES.PRICE * TICKETLINES.UNITS)
        + SUM((TICKETLINES.PRICE * TICKETLINES.UNITS) * TAXES.RATE) AS GROSSTOTAL
FROM TICKETLINES
LEFT OUTER JOIN PRODUCTS ON TICKETLINES.PRODUCT = PRODUCTS.ID
LEFT OUTER JOIN TICKETS ON TICKETS.ID = TICKETLINES.TICKET
LEFT OUTER JOIN RECEIPTS ON RECEIPTS.ID = TICKETS.ID, TAXES
WHERE RECEIPTS.ID = TICKETS.ID AND TICKETS.ID = TICKETLINES.TICKET
AND TICKETLINES.PRODUCT = PRODUCTS.ID
AND TICKETLINES.TAXID = TAXES.ID
GROUP BY PRODUCTS.REFERENCE, PRODUCTS.NAME,PRODUCTS.PRICEBUY,PRODUCTS.PRICESELL
ORDER BY GROSSTOTAL DESC
LIMIT 10

and

SELECT PRODUCTS.ID, PRODUCTS.REFERENCE, PRODUCTS.CODE, PRODUCTS.NAME, PRODUCTS.ISCOM,
       PRODUCTS.ISSCALE, PRODUCTS.PRICEBUY, PRODUCTS.PRICESELL, PRODUCTS.TAXCAT,
       PRODUCTS.CATEGORY,PRODUCTS.ATTRIBUTESET_ID, PRODUCTS.IMAGE, PRODUCTS.ATTRIBUTES,
       PRODUCTS.ISKITCHEN, PRODUCTS.ISSERVICE, PRODUCTS.DISPLAY, PRODUCTS.ISVPRICE,
       PRODUCTS.ISVERPATRIB, PRODUCTS.TEXTTIP, PRODUCTS.WARRANTY, PRODUCTS.STOCKUNITS,
       TAXES.NAME, TAXES.RATE, PRODUCTS.STOCKVOLUME
FROM PRODUCTS
INNER JOIN PRODUCTS_CAT ON PRODUCTS.ID=PRODUCTS_CAT.PRODUCT
JOIN TAXCATEGORIES ON PRODUCTS.TAXCAT=TAXCATEGORIES.ID
JOIN TAXES ON TAXCATEGORIES.ID=TAXES.ID
ORDER BY PRODUCTS.NAME

Now, I am trying to combine these two statements into one, here is what I’ve got:

SELECT PRODUCTS.ID, PRODUCTS.REFERENCE, PRODUCTS.CODE, PRODUCTS.NAME, PRODUCTS.ISCOM,
       PRODUCTS.ISSCALE, PRODUCTS.PRICEBUY, PRODUCTS.PRICESELL, PRODUCTS.TAXCAT,
       PRODUCTS.CATEGORY, PRODUCTS.ATTRIBUTESET_ID, PRODUCTS.IMAGE, PRODUCTS.ATTRIBUTES,
       PRODUCTS.ISKITCHEN, PRODUCTS.ISSERVICE, PRODUCTS.DISPLAY, PRODUCTS.ISVPRICE,
       PRODUCTS.ISVERPATRIB, PRODUCTS.TEXTTIP, PRODUCTS.WARRANTY, PRODUCTS.STOCKUNITS,
       TAXES.NAME, TAXES.RATE, PRODUCTS.STOCKVOLUME,
       SUM(TICKETLINES.UNITS) AS UNITS,
       SUM(TICKETLINES.PRICE*TICKETLINES.UNITS) AS SUBTOTAL,
       SUM((TICKETLINES.PRICE*TICKETLINES.UNITS)*TAXES.RATE) AS TAXESTOTAL,
       SUM(TICKETLINES.PRICE*TICKETLINES.UNITS)
         +SUM((TICKETLINES.PRICE*TICKETLINES.UNITS)*TAXES.RATE) AS GROSSTOTAL
FROM TICKETLINES
INNER JOIN PRODUCTS_CAT ON PRODUCTS.ID=PRODUCTS_CAT.PRODUCT
JOIN TAXCATEGORIES ON PPRODUCTS.TAXCAT=TAXCATEGORIES.ID
JOIN TAXES ON TAXCATEGORIES.ID=TAXES.ID
LEFT OUTER JOIN PRODUCTS ON TICKETLINES.PRODUCT=PRODUCTS.ID
LEFT OUTER JOIN TICKETS ON TICKETS.ID=TICKETLINES.TICKET
LEFT OUTER JOIN RECEIPTS ON RECEIPTS.ID=TICKETS.ID, TAXES
WHERE RECEIPTS.ID=TICKETS.ID AND TICKETS.ID=TICKETLINES.TICKET
AND TICKETLINES.PRODUCT=PRODUCTS.ID
AND TICKETLINES.TAXID=TAXES.ID
ORDER BY PRODUCTS.NAME

Why upper statement does not execute and reports error Error Code: 1066. Not unique table/alias: 'TAXES'
?

Solution 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'

Solution 2

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

Solution 3

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?

Related videos on Youtube