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 asked 06 Oct '13, 01:17 mfkpie8 |
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. answered 06 Oct '13, 07:45 Volker Barth |
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 answered 07 Oct '13, 12:56 mfkpie8 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
|