Why do I get this error? Syntax error or access violation: 1066 Not unique table/alias: 'Employees'

Question

Here's my SQL query:

SELECT Employees.employee_id AS `Employees__employee_id`, 
    Employees.designation_id AS `Employees__designation_id`, 
    Employees.first_name AS `Employees__first_name`, 
    Employees.last_name AS `Employees__last_name`, 
    Employees.address AS `Employees__address`, 
    Employees.contact_no AS `Employees__contact_no`, 
    Employees.joining_date AS `Employees__joining_date`, 
    Employees.username AS `Employees__username`, 
    Employees.password AS `Employees__password`, 
    Employees.basic_pay AS `Employees__basic_pay`, 
    Employees.create_date AS `Employees__create_date`, 
    Employees.status AS `Employees__status`, 
    Designations.designation_id AS `Designations__designation_id`, 
    Designations.designation_name AS `Designations__designation_name`, 
    Designations.description AS `Designations__description` 
FROM employees Employees 
INNER JOIN employees Employees 
    ON Employees.employee_id = (Employees.employee_id) 
INNER JOIN designations Designations 
    ON Designations.designation_id = (Employees.designation_id) 
LIMIT 20 OFFSET 0

The ERROR I get is:

Error: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'Employees'!

Can you help me in this.

Answer 1

You can't use the same alias (Employees) for more than one table in your query. Here, you're using it in both the from and the join clauses. Just use a different alias in one of them and you should be OK.

Answer 2

In your FROM clause you're setting a table alias Employees which is OK as long as your MySQL runs case sensitive (like on Linux or Unix) or may fail if you run it on Windows, but later in your INNER JOIN clause you join the table with the same alias. This is a duplicate alias. SQL does not permit duplicate aliases cause otherwise it will not know expression from what table should it evaluate when you call Employees.designation_id for example.

Table alias for the main table and for the joined table must be different no matter if you join the same table or not.

Update: Maybe you don't need to join the same table once more. Drop the inner join employees part:

SELECT Employees.employee_id AS `Employees__employee_id`, 
    Employees.designation_id AS `Employees__designation_id`, 
    Employees.first_name AS `Employees__first_name`, 
    Employees.last_name AS `Employees__last_name`, 
    Employees.address AS `Employees__address`, 
    Employees.contact_no AS `Employees__contact_no`, 
    Employees.joining_date AS `Employees__joining_date`, 
    Employees.username AS `Employees__username`, 
    Employees.password AS `Employees__password`, 
    Employees.basic_pay AS `Employees__basic_pay`, 
    Employees.create_date AS `Employees__create_date`, 
    Employees.status AS `Employees__status`, 
    Designations.designation_id AS `Designations__designation_id`, 
    Designations.designation_name AS `Designations__designation_name`, 
    Designations.description AS `Designations__description` 
FROM employees Employees 

INNER JOIN designations Designations 
    ON Designations.designation_id = (Employees.designation_id) 
LIMIT 20 OFFSET 0