SQL Join Double Table?

Question

I have this table:

 CREATE TABLE EMP
    (EMPNO NUMBER(4) CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY ,
    ENAME varchar2(10),
    JOB varchar2(9),
    MGR NUMBER(4)CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),
    HIREDATE DATE,
    CONSTRAINT EMP_FOREIGN_KEY REFERENCES DEPT (DEPTNO));

And now I want to get the name (ename), job (job) and manager name (mgr) of all employees.

I thought about this with a join in one table:

SELECT ename, job, empno
FROM emp
INNER JOIN emp AS emp1
ON (emp.mgr = emp1.empno);

But I always get the error: "missing keyword"

Answer 1

I think you should remove ASfrom the internal join operator:

SELECT emp.ename, emp.job, emp.empno
FROM emp
INNER JOIN emp  emp1
ON (emp.mgr = emp1.empno);

And you must add an alias in front of each column name.

Answer 2

SELECT E2.ename, E2.job, E2.empno, E1.ename as 'manager_name'
FROM emp as E1
INNER JOIN emp AS E2
ON E1.mgr = E2.empno;

Answer 3

Try the following:

SELECT e.ename, e.job, e.empno
FROM emp e
INNER JOIN emp e1
   ON (e.mgr = e1.empno);