Self-join: SQL parse error on table alias

Question

Using a simple self join to list employees' managers:

CREATE VIEW AS 
SELECT e1.EMP_ID EmployeeId, e1.FNAME EmployeeName,  
       e1.MANAGER ManagerName
FROM   EMPLOYEE e1 
       LEFT JOIN EMPLOYEE e2 
       ON e1.MANAGER = e2.EMP_ID

Where the table in question is EMPLOYEE, with the primary key EMP_ID.

Both MySQL and Oracle return errors for the code; and although I have tried a number of different variations, the main stumbling block is the use of an alias for the table in question (e1 and e2) which neither dbms consider to be legal identifiers.

what is the error ? and are Manager and EMP_ID same ? – Deepanshu Goyal Apr 02 '13 at 13:22 — Deepanshu Goyal, Apr 02 '13 at 13:22

score 3 · Accepted Answer · answered Apr 02 '13 at 13:20

3

You need a name for your view:

CREATE VIEW v_emp AS 
SELECT e1.EMP_ID EmployeeId, e1.FNAME EmployeeName,  
       e1.MANAGER ManagerName
FROM   EMPLOYEE e1 
       LEFT JOIN EMPLOYEE e2 
       ON e1.MANAGER = e2.EMP_ID

answered Apr 02 '13 at 13:20

Gordon Linoff

1,242,037
58
646
786

Thanks: I hate it when it boils down to little more than a typo :P – Stumbler Apr 02 '13 at 13:24

Self-join: SQL parse error on table alias

1 Answers1