Mysql ошибка 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.Not Unique Table Alias

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.

Contents

  • Why Do Your SQL Statements Have No Unique Table or Aliases?
    • – 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
  • How Your SQL Statements Can Have Unique Tables and Aliases?
    • – 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
  • Conclusion

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:

SELECT tech_articles.* , article_sections.title, article_categories.title, account_users.name, account_users.name

FROM tech_articles

INNER JOIN article_sections ON tech_articles.section_id = article_sections.id

INNER JOIN article_categories ON tech_articles.category_id = article_categories.id

INNER JOIN account_users ON tech_articles.author_id = account_users.id

LEFT JOIN account_users ON tech_articles.modified_by = account_users.id

WHERE tech_articles.id = ‘1’

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.

SELECT software_projects.sp_ID, sp_Title, engineers_account.ea_ID, uName, access_Level

FROM software_projects

JOIN engineers_account

ON software_projects.AccountID = engineers_account.ea_ID

JOIN Project

ON software_projects.sp_ID = Project.Project_ID

where access_Level = ‘Client’;

– 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”.

SELECT Shares.share_price, InvestorsShares.share_id, InvestorsShares.Quantity

FROM Shares

LEFT JOIN Shares on Shares.share_id = InvestorsShares.share_id

WHERE Shares.share_id = <random_number />

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.

<?php

$this->database_connection->select(‘*’);

$this->database_connection->from(‘web_service’);

$this->database_connection->join(‘user’, ‘user.u_email = web_service.u_email’, ‘inner’);

$this->database_connection->join(‘web_service’, ‘web_service.u_email = user.u_email’, ‘inner’);

$query = $this->database_connection->get();

?>

– 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.Not Unique Table Alias Causes

– 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.

const qb = getRepository(name_of_your_entity_class)

.createQueryBuilder(“name_of_entity”)

.skip(size * (page – 1))

.take(size)

.orderBy(‘name_of_entity.created_at’, orderBy)

.leftJoinAndSelect(“name_of_entity.approvedBy”, “site_users”)

.leftJoinAndSelect(“name_of_entity.user”, “site_users”)

.select([‘name_of_entity’, ‘site_users.email’, ‘site_users.id’, ‘site_users.first_name’, ‘site_users.last_name’])

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.

models.Order.findOne({

include: [

{

model: models.Provider,

attributes: [‘id’,’userId’],

include : [{

model : models.AccountHolder,

attributes: [‘first_name’,’last_name’,’phone_number’]

},{

model : models.AccountHolder,

attributes: [‘phone_number’]

}]

}

]

})

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.

SELECT tech_articles . * , article_sections.title, article_categories.title, account_users.name, alias_account_users.name

FROM tech_articles

INNER JOIN article_sections ON tech_articles.section_id = article_sections.id

INNER JOIN article_categories ON tech_articles.category_id = article_categories.id

INNER JOIN account_users ON tech_articles.author_id = account_users.id

LEFT JOIN account_users alias_account_users ON tech_articles.modified_by = alias_account_users.id

WHERE tech_articles.id = ‘1’

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.

SELECT sp.sp_ID, p.p_Title, acc.ea_ID, acc.uName, acc.access_Level, c.fName, c.lName

FROM software_projects sp

INNER JOIN engineers_account acc

ON sp.AccountID = acc.ea_ID

INNER JOIN Project p

ON sp.sp_ID = p.p_ID

INNER JOIN Clients c

ON acc.ea_ID = c.ea_ID

WHERE acc.access_Level = ‘Client’;

– 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.

SELECT Shares.share_price, InvestorsShares.share_id, InvestorsShares.Quantity

FROM Shares

LEFT JOIN InvestorsShares on Shares.share_id = InvestorsShares.share_id

WHERE Shares.share_id = <random_number />

Up next, the following is the correct sample of the “CodeIgniter” code that caused an error.

$this->database_connection->select(‘*’);

$this->database_connection->from(‘web_service’);

$this->database_connection->join(‘user’, ‘user.e_email = web_service.e_email’, ‘inner’);

$query = $this->database_connection->get();

– 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.Not Unique Table Alias Fixes

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.

const qb = getRepository(name_of_your_entity_class)

.createQueryBuilder(“name_of_entity”)

.skip(size * (page – 1))

.take(size)

.orderBy(‘name_of_entity.created_at’, orderBy)

.leftJoinAndSelect(“name_of_entity.approvedBy”, “first_site_users”)

.leftJoinAndSelect(“name_of_entity.user”, “second_site_users”)

.select(

[

‘name_of_entity’,

‘first_site_users.email’,

‘first_site_users.id’,

‘first_site_users.first_name’,

‘first_site_users.last_name’,

‘first_site_users.email’,

‘second_site_users.id’,

‘second_site_users.first_name’,

‘second_site_users.last_name’

]);

– 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.

Provider.belongsTo/haveMany/any…(AccountHolder, {as: ‘AH_1’});

Provider.belongsTo/haveMany/any…(AccountHolder, {as: ‘AH_2’});

include: [{

model: models.Provider,

attributes: [‘id’, ‘userId’],

include: [{

model: models.AccountHolder,

as : ‘AH_2’ // Use the alias here

attributes: [‘first_name’, ‘last_name’, ‘phone_number’]

}, {

model: models.AccountHolder,

as : ‘AH_1’ // And here as well.

attributes: [‘phone_number’]

}]

}]

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.

  • Author
  • Recent Posts

Position is Everything

Your Go-To Resource for Learn & Build: CSS,JavaScript,HTML,PHP,C++ and MYSQL. Meet The Team

Position is Everything

Источник

I got a problem when I was trying to join 3 tables.

Below are the mysql syntax i was using :

SELECT
FROM carecell
LEFT JOIN staff ON cc.id_pks = staff.id_emp
LEFT JOIN staff ON cc.id_wpks = staff.id_emp
INNER JOIN member ON member.id_member = staff.id_member

Please Help me.. What should I do in order to fix the syntax?

asked Aug 19, 2014 at 10:30

Glenn Maramis's user avatar

The SQL engine cannot distinguish between the two staff tables in the from clause. You need to give them different names. Something like this:

FROM carecell cc LEFT JOIN
     staff s1
     ON cc.id_pks = s1.id_emp LEFT JOIN
     staff s2
     ON cc.id_wpks = s2 .id_emp INNER JOIN
     member m
     ON m.id_member = s2.id_member

answered Aug 19, 2014 at 10:33

Gordon Linoff's user avatar

Gordon LinoffGordon Linoff

1.2m57 gold badges639 silver badges781 bronze badges

If you join the same table multiple times you need to give that table each time a different alias name so the DB engine can differ between them

SELECT *
FROM carecell as cc
LEFT JOIN staff as s1 ON cc.id_pks = s1.id_emp
LEFT JOIN staff as s2 ON cc.id_wpks = s2.id_emp
INNER JOIN member as m ON m.id_member = s1.id_member

You already used aliases since you gave carecell the alias cc. In the last line of your query I joined s1 but you have to decide which one to take — s1 or s2

answered Aug 19, 2014 at 10:33

juergen d's user avatar

juergen djuergen d

201k36 gold badges290 silver badges357 bronze badges 

SELECT *
FROM carecell
LEFT JOIN staff t1 ON cc.id_pks = t1.id_emp
LEFT JOIN staff t2 ON cc.id_wpks = t2.id_emp
INNER JOIN member ON member.id_member = t1.id_member

answered Aug 19, 2014 at 10:33

Jaydee's user avatar

JaydeeJaydee

4,1281 gold badge18 silver badges20 bronze badges

0

Источник

You joined clients twice in the same FROM clause, that’s throwing the error.

The two incarnations of the table need to have different aliases (chosen: cl_to and cl_from):

SELECT request.*, 
       cl_to  .ClientName AS ToClient, 
       cl_from.ClientName AS FromClient, 
       drivers.DriName, requesttype.ReqTName 
FROM request
    INNER JOIN clients AS cl_to    -- alias 1 
        ON cl_to.ClientID = request.ReqToClient
    INNER JOIN clients AS cl_from   -- alias 2
        ON cl_from.ClientID = request.ReqFromClient
    INNER JOIN drivers
        ON drivers.DriID = request.DriID
    INNER JOIN requesttype
        ON requesttype.ReqTID = request.ReqTID ;

I’d prefer to use (short) aliases for all tables:

SELECT rq.*, 
       cl_to  .ClientName AS ToClient, 
       cl_from.ClientName AS FromClient, 
       dr.DriName, rt.ReqTName 
FROM request AS rq
    INNER JOIN clients AS cl_to   ON cl_to.ClientID = rq.ReqToClient
    INNER JOIN clients AS cl_from ON cl_from.ClientID = rq.ReqFromClient
    INNER JOIN drivers AS dr      ON dr.DriID = rq.DriID
    INNER JOIN requesttype AS rt  ON rt.ReqTID = rq.ReqTID ;

Источник

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

Источник

Понравилась статья? Поделить с друзьями:
  • Mysql ошибка 1059
  • Mysql ошибка 1045 при установке
  • Mysql ошибка 101
  • Mysql код ошибки 2005
  • Mysql код ошибки 2002