Find duplicate/repeated rows in hierarchical sql

Question

I'm attempting to detect duplicated/repeated values within a hierarchical table.

Consider the following (slightly contrived) example:

SELECT *
FROM   emp
START WITH mgr IN (SELECT empno FROM emp WHERE ename = 'JONES'
                   UNION ALL
                   SELECT empno FROM emp WHERE ename = 'JONES')
CONNECT BY PRIOR empno = mgr;

Returns...

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

What I actually want is...

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

ie I want each row to be returned as many times as it exists in the sub-query (ignoring the order). Since the START WITH is using an IN clause, the repeated values are being suppressed. Is it possible to reorganise the SQL so that I can do this?

Note that in my case the sub-clause is not a UNION, but is a SELECT which may return multiple (possibly duplicate) values from a table.

I could do it in PL/SQL by writing the values into a temp table and then GROUPing + COUNTing, but I'd prefer to do it in SQL only if possible.

Let me know if any clarification is required.

Thanks :-)

EDIT:

Note that there may be 0...N values returned from the sub-query.

Answer 1

Hi,

it's quite easy:

SELECT *FROM empSTART WITH mgr IN (SELECT empno FROM emp WHERE ename = 'JONES' UNION ALL
SELECT empno FROM emp WHERE ename = 'JONES')CONNECT BY PRIOR empno = mgr;

Answer 2

Hi cagcowboy

One way to duplicate a result set is to cross join (cartesian product) it to a result set with two rows, ie:

SQL> WITH your_query AS (
  2     SELECT object_name
  3       FROM all_objects WHERE ROWNUM <= 3
  4  )
  5  SELECT your_query.*
  6    FROM your_query
  7   CROSS JOIN (SELECT NULL FROM dual UNION ALL SELECT NULL FROM dual);

OBJECT_NAME
------------------------------
IND$
IND$
ICOL$
ICOL$
OBJ$
OBJ$

In your case this should work:

WITH your_query AS (
   SELECT *
     FROM emp
    START WITH mgr IN (SELECT empno FROM emp WHERE ename = 'JONES')
          CONNECT BY PRIOR empno = mgr
)
SELECT your_query.*
  FROM your_query
 CROSS JOIN (SELECT NULL FROM dual UNION ALL SELECT NULL FROM dual);

Answer 3

Try This one..

SELECT  EMPNO,ENAME FROM,count(*)as counts   emp group by EMPNO,ENAME having count(*)>1

Answer 4

Sounds like you need to first do an outer join from emp to the result of your complex selection query, and then base the connect-by query on that.

Something like this, maybe:

WITH mgrs AS (
   SELECT empno FROM emp WHERE ename = 'JONES'
   UNION ALL
   SELECT empno FROM emp WHERE ename = 'JONES'
),
all_emps AS (
   SELECT emp.*, 
          CASE WHEN mgrs.empno IS NOT NULL THEN 1 END AS start_with  
   FROM emp
   LEFT OUTER JOIN mgrs ON mgrs.empno = emp.mgr
)
SELECT *
FROM all_emps
START WITH start_with = 1
CONNECT BY PRIOR empno = mgr;