Re: Self join help..

From: <mjain_at_my-dejanews.com>
Date: Sun, 08 Nov 1998 23:25:28 GMT
Message-ID: <725998$un3$1@nnrp1.dejanews.com>

Hi,

Try my query as given below:-

select a.empno Mgr_Emp_No, a.ename Mgr_Name from emp a where a.empno in (

   select mgrid from

       (
         select b.mgr mgrid, count(*) no_of_sub_ordinates from emp b
         where b.mgr is not null
         group by b.mgr
         having count(*) > 2
       )

)
/

Example:-

SQL> select a.empno Mgr_Emp_No, a.ename Mgr_Name from emp a   2 where a.empno in (

  3     select mgrid from
  4         (
  5           select b.mgr mgrid, count(*) no_of_sub_ordinates from emp b
  6           where b.mgr is not null
  7           group by b.mgr
  8           having count(*) > 2
  9         )

 10 );

MGR_EMP_NO MGR_NAME
---------- ----------

      7698 BLAKE
      7839 KING

SQL> Hope this helps,

Manoj Jain
Oracle Certified Professional - OCP DBA Chauncey Certified Oracle7 DBA

In article <723aqt$g8c$1_at_nnrp1.dejanews.com>,   shirushah_at_my-dejanews.com wrote:
> Need a list of all enames and their empno's who manage
> more than one person. For eg. need a statment that will
> show me only King, Blake & James as they manage more than
> 2 people.
>
> Data from employee table:
> EMPNO ENAME MGR
> ------ ---------- ---------
> 7839 KING
> 7698 BLAKE 7839
> 7782 CLARK 7839
> 7566 JONES 7839
> 7654 MARTIN 7698
> 7499 ALLEN 7698
> 7844 TURNER 7698
> 7900 JAMES 7698
> 7521 WARD 7698
> 7902 FORD 7566
> 7369 SMITH 7902
> 7788 SCOTT 7566
> 7876 ADAMS 7788
> 7934 MILLER 7782
>
> Thanx Very Much..
> Shiru S
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Sun Nov 08 1998 - 17:25:28 CST