Question Re: Recursion according to the first layer in Technology Q&A

Recursion according to the first layer

ximen — Sun, 06 Oct 2013 01:17:26 GMT

CREATE TABLE ORG( 
  EMPID INTEGER NOT NULL, 
  EMPNAME VARCHAR(128) NOT NULL, 
  MGRID INTEGER NOT NULL);

 INSERT INTO ORG VALUES(1, 'Jack', 0); 
 INSERT INTO ORG VALUES(2, 'Mary', 1); 
 INSERT INTO ORG VALUES(3, 'Tom', 1); 
 INSERT INTO ORG VALUES(4, 'Ben', 2); 
 INSERT INTO ORG VALUES(5, 'John', 3); 
 INSERT INTO ORG VALUES(6, 'Emily', 3); 
 INSERT INTO ORG VALUES(7, 'Kate', 3); 
 INSERT INTO ORG VALUES(8, 'Mark', 6);

RESULT:Each branch of want EMPID = 1

MGRID   EMPNAME   EMPID
TOM       JOHN      3
TOM      EMILY      3
TOM      KATE       4
TOM      mark       6
MARK     BEN          2

Re: Recursion according to the first layer

VolkerBarth — Sun, 06 Oct 2013 09:45:08 GMT

Oh well. That has been given as hint to you before: Have you read and studied docs on recursive query?

For example, Breck has given several thorough tutorials in his blog, and they use a nearly identical sample with manager/employees:

Once you have built the recursive structure, it's usually simple to then build the desired filtering - I'm not sure I understand your actual question, however, if you want to list all employees that are managed by a particular person (including that person), you could use something like the query from the second cited (from "Answer 1: Breadth-First Traversal") and add a WHERE clause like:

WITH RECURSIVE breadth_first_traversal
   ( level,
     lineage,
     employee_id,
     manager_id,
     name )
AS ( SELECT CAST ( 1 AS INTEGER )          AS level,
            CAST ( employee.name 
                   AS LONG VARCHAR )       AS lineage,
            employee.employee_id           AS employee_id,
            employee.manager_id            AS manager_id,
            employee.name                  AS name
       FROM employee
      WHERE employee.employee_id = employee.manager_id
     UNION ALL
     SELECT breadth_first_traversal.level + 1,
            STRING ( breadth_first_traversal.lineage,
                     '-',
                     employee.name ),
            employee.employee_id,
            employee.manager_id,
            employee.name
       FROM breadth_first_traversal
            INNER JOIN employee
                    ON employee.manager_id = breadth_first_traversal.employee_id
      WHERE employee.manager_id <> employee.employee_id )
SELECT employee_id,
       level,
       lineage
  FROM breadth_first_traversal
  WHERE lineage like '%Calista%' --< added filter
 ORDER BY level,
       lineage DESC;

That will return:

I'd really recommend that you study these samples step by step and play with them to get different answers by modifying them slightly. Then, if you have particular questions, feel free to ask again. But don't expect us to do your homework - at least don't expect me to do so.

Re: Recursion according to the first layer

ximen — Mon, 07 Oct 2013 12:56:21 GMT

 with recursive ps(empid,empname,Parent_id) as(
select empid,empname,Parent_id=convert(varchar(100),empname) from org where mgrid=1
union all
select org.empid,org.empname,Parent_id=convert(varchar(100),ps.parent_id) from ps,org where org.mgrid=ps.empid)
select * from ps

 2,Mary,Mary
4,Ben,Mary
3,Tom,Tom
7,Kate,Tom
6,Emily,Tom
5,John,Tom
8,Mark,Tom

Re: Recursion according to the first layer

VolkerBarth — Wed, 09 Oct 2013 09:22:07 GMT

Just in order to understand:

Is that the query with your desired result or do you want to get further advice?

(I would think that the managers of empid 2, 3 and 8 are not correct when compared to your question, but that's just another wild guess here...)