Error 1066 – SQLSTATE: 42000 (ER_NONUNIQ_TABLE) “Not unique table/alias: ‘%s'” in MySQL

by

Encountering Error 1066 in MySQL indicates that there is an issue with table aliases in your query. This error occurs when you join multiple tables in a query and either do not assign an alias to tables with the same name or use the same alias for more than one table. Understanding and resolving this issue is crucial for the query to execute successfully.

Diagnosing the Error

To diagnose Error 1066, review the SQL query for the following common mistakes:

  1. Check if you are joining two tables with the same name without using aliases to differentiate them.
  2. Ensure that each table alias is unique within the scope of your query.
  3. Look for typos or copy-paste errors that might have caused you to inadvertently use the same alias more than once.

Fixing the Error

Here are some examples and sample code to illustrate how to fix Error 1066 in different situations:

Example 1: Joining the Same Table Twice Without Aliases

SELECT * FROM employees, employees WHERE employees.manager_id = employees.id;

The above query will cause Error 1066 because it attempts to join the employees table with itself without using aliases. Here’s the fix:

SELECT * FROM employees AS e1, employees AS e2 WHERE e1.manager_id = e2.id;

Example 2: Using the Same Alias for Different Tables

SELECT * FROM orders AS o
JOIN customers AS o ON o.customer_id = o.id;

This query incorrectly uses the alias o for both orders and customers tables. The corrected query would be:

SELECT * FROM orders AS ord
JOIN customers AS cust ON ord.customer_id = cust.id;

Example 3: Joining Multiple Instances of the Same Table

When you need to join a table to itself multiple times (self-join), use distinct aliases each time:

SELECT e1.name AS Employee, e2.name AS Manager
FROM employees AS e1
JOIN employees AS e2 ON e1.manager_id = e2.id
JOIN employees AS e3 ON e1.some_other_id = e3.id;

In this example, e1, e2, and e3 are unique aliases for each instance of the employees table.

Example 4: Complex Queries with Multiple Joins

In complex queries with multiple joins, it’s easy to lose track of aliases. Always double-check your aliases:

SELECT p.product_name, o.order_date
FROM products AS p
JOIN orders AS o ON p.id = o.product_id
JOIN customers AS c ON o.customer_id = c.id
JOIN orders AS o2 ON c.id = o2.customer_id; -- Error here

The alias o2 is used correctly, but the alias o is reused, causing Error 1066. The fix is to use another unique alias for the second join with the orders table.

Conclusion

To prevent Error 1066, always ensure that each table in your query has a unique alias, especially when performing self-joins or joining multiple tables in complex queries. Careful aliasing and attention to detail will help you avoid this common SQL error and maintain clear, readable, and functional SQL code.

Leave a Comment