How to write a recursive query?

Question

There is a dept_employees hierarchy dept_employees

 | ID | parent_id | obj_id | obj_type | +----+-----------+--------+----------+ | 10 | null | 1 | dept | | 11 | 10 | 2 | dept | | 12 | 11 | 1 | emp | | 13 | 11 | 2 | emp | | 14 | 13 | 3 | emp | | 15 | 12 | 4 | emp |

The hierarchical table stores the list of departments and employees.
obj_id refers to the employees and ogpo_dept
And there are tables of employees, departments and point-of-sale terminals:

 dbo.employees dbo.ogpo_dept dbo.cash_extensions | ID | FIO | dept_id | | ID | Name | | ID | FIO | +----+------+---------| +----+--------+ +----+---------+ | 1 | Аня | 1 | | 1 | ГО | | 1 | Аня | | 2 | Ира | 1 | | 2 | Астана | | 2 | Макс | | 3 | Макс | 1 | | 3 | Юля | | 4 | Юля | 1 |

I need to transfer the employee id and pull out from the hierarchical table look from the employee (who passed the ID) to the upstream and downstream employees who are in the dbo.cash_extensions table
My recursive query:

 with cteTop as ( --первый cte выбирает вышестоящую сущность select de.id, de.parent_id, de.obj_id from [dbo].[dept_employees] de where de.id = 12 union all select de.id, de.parent_id, de.obj_id from [dbo].[dept_employees] de inner join cteTop c on c.parent_id = de.id ), cteBot as ( -- выбираем нижестоящих select de.id, de.parent_id, de.obj_id from [dbo].[dept_employees] de where de.id = 12 union all select de.id, de.parent_id, de.obj_id from [dbo].[dept_employees] de inner join cteBot c on c.id = de.parent_id ) select e.fio from cteTop left join ogpo_dept od on od.id = cteTop.obj_id left join employees e on e.dept_id = od.id inner join Cash.dbo.cash_extensions ce on ce.fio=e.fio union all select e.fio from cteBot left join ogpo_dept od on od.id = cteBot.obj_id left join employees e on e.dept_id = od.id inner join cash_extensions ce on ce.fio=e.fio

Query result:

 | FIO | +-----+ | Аня | | Юля |

The result should be:

 | FIO | +------+ | Аня | | Юля | | Макс |

The question is how to pull out all the employees of the higher branch and downstream employees from the current employee?

rechecked the subordination of employees shown in the tables, and it turns out that it is the first result that is correct (and not the second, as stated).
2 branches, one subordinate to another, all people belong to a subordinate branch.
Further in this branch there are 2 chains of 2 subordinates one to another employee: Anya-Yulia and Ira-Max.
@pegoopik - in this case, both examples are wrong, do not show the 4th employee.
well, the meaning goes away altogether and the task is simplified - we simply output ALL of the staff of this branch (but also recursively), I think you incorrectly assumed
@pegoopik, then - the correct first of the results, because Max is not subordinate to Ani (whose id is transferred to the input), Because in the assignment: <I need to transfer the employee's id and pull it out of the hierarchical table from the employee (who passed the ID) upstream and downstream employees who are in the dbo.cash_extensions table>
@pegoopik - that is, the author of the question (if I'm not mistaken with Max) - already has a query variant that gives the correct result, only the author considers it a mistake, contrary to the rules that he himself outlined
@pegoopik, I do not know, the author repeats this condition 2 times - see the last sentence in his question.
Apparently he himself floats in the logic of his question, and perhaps he doesn’t really need it, since we are here for him (without his participation))))) trying to streamline his thoughts

Hamster 18k five 33 83 · Answer 1 · 2016-03-30T05:34:39

I will briefly describe the algorithm.

If I understand correctly, the task consists of three parts:

From the transmitted ID, we go up the tree until we find a department.
from the department we find all his children, we choose from them only with the type of employee
we select those who are in the cache_extensions table

paragraphs 1. and 2. first and second recursive part of recursive CTE:

--plates

 USE tempdb; IF OBJECT_ID('dept_employees')IS NOT NULL DROP TABLE dept_employees IF OBJECT_ID('employees')IS NOT NULL DROP TABLE employees IF OBJECT_ID('ogpo_dept')IS NOT NULL DROP TABLE ogpo_dept IF OBJECT_ID('cash_extensions')IS NOT NULL DROP TABLE cash_extensions CREATE TABLE dept_employees( ID INT NOT NULL, parent_id INT NULL, obj_id INT NOT NULL, obj_type VARCHAR(4) NOT NULL ) CREATE TABLE employees( ID INT NOT NULL, FIO VARCHAR(255) NOT NULL, dept_id INT NOT NULL ) /*CREATE TABLE ogpo_dept( ID INT NOT NULL, Name VARCHAR(255) NOT NULL )*/ CREATE TABLE cash_extensions( ID INT NOT NULL, FIO VARCHAR(255) NOT NULL )

--data

 INSERT dept_employees VALUES --depts (10, NULL, 1, 'dept'), (20, 10, 2, 'dept'), (30, NULL, 3, 'dept'), (40, 30, 4, 'dept'), --emps (50, 20, 1, 'emp'), (60, 50, 2, 'emp'), (70, 50, 3, 'emp'), (80, 40, 4, 'emp'), (90, 80, 5, 'emp'), (100,30, 6, 'emp') INSERT employees VALUES (1, 'd1->d2->p1', 2), (2, 'd1->d2->p1->p2', 2), (3, 'd1->d2->p1->p3', 2), (4, 'd3->d4->p4', 4), (5, 'd3->d4->p4->p5', 4), (6, 'd3->p6', 3) INSERT cash_extensions VALUES (1, 'd1->d2->p1'), (2, 'd1->d2->p1->p2'), (3, 'd1->d2->p1->p3'), (4, 'd3->d4->p4'), (5, 'd3->d4->p4->p5'), (6, 'd3->p6')

--procedure

 IF OBJECT_ID('FindAllCasheExtemsionsFromDeptByEmployee') IS NOT NULL DROP PROC FindAllCasheExtemsionsFromDeptByEmployee GO CREATE PROC FindAllCasheExtemsionsFromDeptByEmployee @EmployeeId INT AS WITH CTE AS( SELECT CASE obj_type WHEN 'emp' THEN 'find_depart' WHEN 'dept' THEN 'find_childs' END what, id AS child_Id, parent_id AS parend_id, obj_id, E.obj_type FROM dept_employees E WHERE id = @EmployeeId UNION ALL --идём вверх по дереву до первого департамента SELECT CASE E.obj_type WHEN 'emp' THEN 'find_depart' WHEN 'dept' THEN 'find_childs' END what, id AS child_Id, parent_id AS parend_id, E.obj_id, E.obj_type FROM CTE JOIN dept_employees E ON CTE.parend_id = E.ID WHERE what = 'find_depart' UNION ALL --теперь идём вниз по дереву, находим всех потомков SELECT 'find_childs' what, id AS child_Id, parent_id AS parend_id, E.obj_id, E.obj_type FROM CTE JOIN dept_employees E ON CTE.child_id = E.parent_id WHERE what = 'find_childs' ) SELECT E.* FROM CTE JOIN employees E ON CTE.obj_id = E.ID JOIN cash_extensions C ON E.FIO = C.FIO WHERE CTE.obj_type = 'emp' AND CTE.what = 'find_childs' OPTION (MAXRECURSION 0) GO

- tests and results

 EXEC FindAllCasheExtemsionsFromDeptByEmployee 10 -- d1 /* ID FIO dept_id 1 d1->d2->p1 2 2 d1->d2->p1->p2 2 3 d1->d2->p1->p3 2 */ EXEC FindAllCasheExtemsionsFromDeptByEmployee 50 -- p1 /* ID FIO dept_id 1 d1->d2->p1 2 2 d1->d2->p1->p2 2 3 d1->d2->p1->p3 2 */ EXEC FindAllCasheExtemsionsFromDeptByEmployee 70 -- p3 /* ID FIO dept_id 1 d1->d2->p1 2 2 d1->d2->p1->p2 2 3 d1->d2->p1->p3 2 */ EXEC FindAllCasheExtemsionsFromDeptByEmployee 40 -- d4 /* ID FIO dept_id 4 d3->d4->p4 4 5 d3->d4->p4->p5 4 */ EXEC FindAllCasheExtemsionsFromDeptByEmployee 90 -- p5 /* ID FIO dept_id 4 d3->d4->p4 4 5 d3->d4->p4->p5 4 */ EXEC FindAllCasheExtemsionsFromDeptByEmployee 100 -- p6 /* ID FIO dept_id 4 d3->d4->p4 4 5 d3->d4->p4->p5 4 6 d3->p6 3 */

ps: the task is very poorly described.

Answer 2 · 2016-03-31T07:00:28

My request passed a variable:

 declare @deptID int with cteTop as ( select de.id, de.parent_id, de.obj_id from [dbo].[dept_employees] de where de.id = 12 union all select de.id, de.parent_id, de.obj_id from [dbo].[dept_employees] de inner join cteTop c on c.parent_id = de.id ) select @deptID = cteTop.id from cteTop; --нашли департамент with cteBot as ( select de.id, de.parent_id, de.obj_id from [dbo].[dept_employees] de where de.id = @deptID union all select de.id, de.parent_id, de.obj_id from [dbo].[dept_employees] de inner join cteBot c on c.id = de.parent_id ) select e.fio from cteBot left join employees e on e.id = cteBot.obj_id and cteBot.obj_type = 'emp' inner join cash_extensions ce on ce.fio=e.fio

it is meaningless, you will always find the same department.
a variable is assigned a value as many times as iterations will be in recursion.

How to write a recursive query?

2 answers 2

More articles: