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

alt text

asked 06 Oct '13, 01:17

mfkpie8's gravatar image

mfkpie8
273667075
accept rate: 12%

edited 06 Oct '13, 03:38


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:

alt text

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.

permanent link

answered 06 Oct '13, 07:45

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 06 Oct '13, 07:48

 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
permanent link

answered 07 Oct '13, 12:56

mfkpie8's gravatar image

mfkpie8
273667075
accept rate: 12%

edited 07 Oct '13, 12:58

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...)

(09 Oct '13, 09:22) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×18

question asked: 06 Oct '13, 01:17

question was seen: 2,052 times

last updated: 09 Oct '13, 09:22