Re: explain self join ?

On Sat, 29 Oct 2005 01:28:17 -0700, Oraclegroups wrote:

> Hi!
> I have a table with the table name EMP with 3 columns
> empid,empname,managerid.
> How can i get the empname and managername by using the above table
> information?
> Can u please explain it?
> Ok thank you.........

Oh, and while we are at it:

select w.empno,w.ename,w.job,w.deptno,b.ename as PHB from emp w,emp b
where b.empno(+)=w.mgr
order by deptno

SQL> /      EMPNO ENAME JOB DEPTNO PHB ---------- ---------- --------- ---------- ----------

      7782 CLARK      MANAGER           10 KING
      7839 KING       PRESIDENT         10
      7934 MILLER     CLERK             10 CLARK
      7566 JONES      MANAGER           20 KING
      7902 FORD       ANALYST           20 JONES
      7876 ADAMS      CLERK             20 SCOTT
      7369 SMITH      CLERK             20 FORD
      7788 SCOTT      ANALYST           20 JONES
      7521 WARD       SALESMAN          30 BLAKE
      7844 TURNER     SALESMAN          30 BLAKE
      7499 ALLEN      SALESMAN          30 BLAKE
      7900 JAMES      CLERK             30 BLAKE
      7698 BLAKE      MANAGER           30 KING
      7654 MARTIN     SALESMAN          30 BLAKE

14 rows selected.

That is an example of self join.

-- 
http://www.mgogala.com