Oracle SQL >> Hierarchical Query for parent, child and grandchild in same table

Question

I have the data in my table like below structure,

Manager Id  Employee id chartfield
SM1           MGR-1        12
SM2           MGR-1        12
MGR-1         LEAD-1       12
MGR-1         LEAD-2       12
MGR-1         LEAD-3       12
LEAD-1        LEAD-2       12
LEAD-1      ASSOCIATE -1    12
LEAD-1      ASSOCIATE -2    12
LEAD-2       LEAD-3         12
LEAD-2      ASSOCIATE -3    12
LEAD-2      ASSOCIATE -4    12
LEAD-3      ASSOCIATE -5    12
LEAD-3      ASSOCIATE -6    12
ASSOCIATE -1    JUNIOR - 1  12
ASSOCIATE -1    JUNIOR - 2  12
ASSOCIATE -2    JUNIOR - 1  12
ASSOCIATE -2    JUNIOR - 2  12

I am expecting output with manager and employees with the levels they are present. The only criteria here is same employee could report to multiple managers and this case we should select only distinct employees who is reporting to that employees with least level. In the above case LEAD-2 is reporting to MGR -1 and LEAD-1 but we are considering the least level.

The sample output could be like below structure,

Manager Id  Employee id chartfield  LEVEL1
MGR-1   LEAD-1           12           1
MGR-1   LEAD-2           12           1
MGR-1   LEAD-3           12           1
MGR-1   ASSOCIATE -1    12            2
MGR-1   ASSOCIATE -2    12            2
MGR-1   ASSOCIATE -3    12            2
MGR-1   ASSOCIATE -4    12            2
MGR-1   ASSOCIATE -5    12            2
MGR-1   ASSOCIATE -6    12            2
MGR-1   JUNIOR - 1      12            3
MGR-1   JUNIOR - 2      12            3

http://stackoverflow.com/questions/tagged/oracle+recursive-query or http://stackoverflow.com/questions/tagged/oracle+connect-by — , Feb 21 '17 at 07:38
And what about MGR-1 reporting to SM1 and SM2 (both seem to be on the same level)? Should both records be retained in the output or just one? — miazo, Feb 21 '17 at 09:02
googly: You replied to an Answer 7 hours ago (at the time I am writing this comment). The same forum member, miazo, asked you the question above 12 hours ago. You came here, you replied to his Answer, but not to the question above. Why not? It is a very important question, it makes perfect sense, and you need to answer it, or else there is no way anyone can give you a good solution to your problem. — , Feb 21 '17 at 21:18

miazo · Answer 1 · 2017-02-21T13:29:29.223

Your sample data shows that everyone is reporting to SM1 and SM2... Anyway, assuming that duplicate managers should be handled properly i.e. only one should be selected, how about the following query:

WITH t (
  MANAGER_ID,
  EMPLOYEE_ID,
  LEVEL_NUM
) AS (
  SELECT
    e.MANAGER_ID,
    e.MANAGER_ID,
    0 AS LEVEL_NUM
  FROM
    emp e
  WHERE
    e.MANAGER_ID NOT IN (
      SELECT
        e.EMPLOYEE_ID
      FROM
        emp e
      )
  UNION ALL
    SELECT
      t.MANAGER_ID,
      e.EMPLOYEE_ID,
      t.LEVEL_NUM + 1 AS LEVEL_NUM      
    FROM
      emp e
    JOIN
      t
    ON
      e.MANAGER_ID = t.EMPLOYEE_ID
)
SELECT
  MANAGER_ID,
  EMPLOYEE_ID,
  LEVEL_NUM
FROM
  (
    SELECT
      MANAGER_ID,
      EMPLOYEE_ID,
      LEVEL_NUM,
      ROW_NUMBER() OVER (PARTITION BY EMPLOYEE_ID ORDER BY LEVEL_NUM, MANAGER_ID) AS ROW_NUM
    FROM
      t
  ) t
WHERE
  t.ROW_NUM = 1 -- keep only the first manager
AND
  t.LEVEL_NUM > 0  -- exclude top level managers reporting to themselves
ORDER BY
  t.LEVEL_NUM,
  t.MANAGER_ID,
  t.EMPLOYEE_ID;

And a query to deal with cycles in data:

WITH t (
  MANAGER_ID,
  EMPLOYEE_ID,
  PATH,
  LEVEL_NUM
) AS (
  SELECT
    e.MANAGER_ID,
    e.MANAGER_ID,
    '> ' || e.MANAGER_ID AS PATH,
    0 AS LEVEL_NUM
  FROM
    emp e
  UNION ALL
    SELECT
      t.MANAGER_ID,
      e.EMPLOYEE_ID,
      t.PATH || ' > ' || e.EMPLOYEE_ID AS PATH,
      t.LEVEL_NUM + 1 AS LEVEL_NUM
    FROM
      emp e
    JOIN
      t
    ON
      e.MANAGER_ID = t.EMPLOYEE_ID
    WHERE
     t.PATH NOT LIKE '%> ' || e.EMPLOYEE_ID || '%'
)
SELECT
  t.MANAGER_ID,
  t.EMPLOYEE_ID,
  t.LEVEL_NUM
FROM
  (
    SELECT
      t.MANAGER_ID,
      t.EMPLOYEE_ID,
      t.LEVEL_NUM,
      ROW_NUMBER() OVER (PARTITION BY t.MANAGER_ID, t.EMPLOYEE_ID ORDER BY t.LEVEL_NUM) AS ROW_NUM
    FROM
      t
  ) t
WHERE
  t.ROW_NUM = 1 -- keep only the first employee occurance
AND
  t.LEVEL_NUM > 0  -- exclude top level managers reporting to themselves
AND
  t.MANAGER_ID = 'SM1'  -- selected manager
ORDER BY
  t.LEVEL_NUM,
  t.MANAGER_ID,
  t.EMPLOYEE_ID;

In the run time I will give manger id as 'MGR-1' so that it will give all the leads, associates and juniors reports to him. But the above sql is giving cycle redundancy error. — googly, Feb 21 '17 at 10:45
If you get a cycle error then you have cycle in your test data (not the sample data posted here, the first query runs on it just fine). I added the other query to deal with that, please check if this solves your issue. — miazo, Feb 21 '17 at 13:31

Oracle SQL >> Hierarchical Query for parent, child and grandchild in same table

1 Answers1