Re: Self join help..

From: Jurij Modic <jmodic_at_src.si>
Date: Sun, 08 Nov 1998 12:02:29 GMT
Message-ID: <36457e03.2995075@news.siol.net>

On Sun, 08 Nov 1998 05:39:41 GMT, 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
Here are three of possible sollutions:

1)
SELECT x.empno, x.ename FROM emp x, emp y WHERE x.empno = y.mgr
GROUP BY x.empno, x.ename HAVING COUNT(*) >=2;

2)
SELECT empno, ename FROM emp x
WHERE 2 <= (SELECT COUNT(*) FROM emp y

            WHERE y.mgr = x.empno);

3)
SELECT x.empno, x.ename FROM emp x,
  (SELECT mgr, COUNT(*) FROM emp
   GROUP BY mgr HAVING COUNT(*) >= 2) y WHERE x.empno = y.mgr;

>Thanx Very Much..
>Shiru S

HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Sun Nov 08 1998 - 06:02:29 CST