September 2, 2019 at 6:25 pm
Hi,
I'm stuck with a recursion query. Consider a stuff/employee table like this:
drop table if exists TeamFilter.Employee;
drop table if exists TeamFilter.Customer;
GO
drop schema if exists TeamFilter;
GO
create schema TeamFilter;
GO
create table TeamFilter.Employee
(
id int not null primary key,
isManager bit,
managerId int,
usrName varchar(50)
)
;
create table TeamFilter.Customer
(
id int not null identity primary key,
employeeId int not null,
cstName varchar(50)
);
-- Level 0: CEO
insert into TeamFilter.Employee (id, isManager, managerId, usrName) values (1, 1, null, 'CEO');
-- Level 1: Division Managers
insert into TeamFilter.Employee (id, isManager, managerId, usrName) values (2, 1, 1, 'DivM1');
insert into TeamFilter.Employee (id, isManager, managerId, usrName) values (3, 1, 1, 'DivM2');
insert into TeamFilter.Employee (id, isManager, managerId, usrName) values (4, 1, 1, 'DivM3');
-- Level 2: Department Managers
insert into TeamFilter.Employee (id, isManager, managerId, usrName) values (5, 1, 2, 'Div1.DepM1');
insert into TeamFilter.Employee (id, isManager, managerId, usrName) values (6, 1, 2, 'Div1.DepM2');
insert into TeamFilter.Employee (id, isManager, managerId, usrName) values (7, 1, 2, 'Div1.DepM3');
-- Level 3: Team Managers
insert into TeamFilter.Employee (id, isManager, managerId, usrName) values (8, 1, 5, 'Div1.Dep1.TM1');
insert into TeamFilter.Employee (id, isManager, managerId, usrName) values (9, 1, 5, 'Div1.Dep1.TM2');
insert into TeamFilter.Employee (id, isManager, managerId, usrName) values (10, 1, 5, 'Div1.Dep1.TM3');
-- Level 4: Team Members
insert into TeamFilter.Employee (id, isManager, managerId, usrName) values (11, 0, 8, 'Div1.Dep1.Team1.Emp1');
insert into TeamFilter.Employee (id, isManager, managerId, usrName) values (12, 0, 8, 'Div1.Dep1.Team1.Emp2');
insert into TeamFilter.Employee (id, isManager, managerId, usrName) values (13, 0, 8, 'Div1.Dep1.Team1.Emp3');
insert into TeamFilter.Employee (id, isManager, managerId, usrName) values (14, 0, 9, 'Div1.Dep1.Team2.Emp4');
insert into TeamFilter.Employee (id, isManager, managerId, usrName) values (15, 0, 9, 'Div1.Dep1.Team2.Emp5');
insert into TeamFilter.Employee (id, isManager, managerId, usrName) values (16, 0, 10, 'Div1.Dep1.Team3.Emp6');
Let's focus on the team with managerId = 8
As you can see, the table contains a record for each member of the team, excluding the manger. The expected result is a list of all team members including their manager - for any given employee. Hence, I have to "produce" one record somehow.
The result would look like this:
empId teamMemberId isManager
12 11 0
12 12 0
12 13 0
12 8 1 -- added
September 2, 2019 at 10:09 pm
This is a standard recursive common table expression. Here's an article showing the solution: https://blog.sqlauthority.com/2008/07/28/sql-server-simple-example-of-recursive-cte/
WITH cteEmpH(EmployeeID, ManagerID, usrName, lvl)
AS (
SELECT ID, ManagerID, usrName, 1 AS lvl
FROM Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ID, e.ManagerID, e.usrName, 1+lvl
FROM Employee e
INNER JOIN cteEmpH ON cteEmpH.EmployeeID = e.ManagerID
)
SELECT *
FROM cteEmpH;
September 3, 2019 at 4:56 pm
Let's correct your DDL first. You really have only one employee and one customer, as your table names tell us? Probably not. Because identifiers are on a nominal scale, they are never numeric. This is simple basic data modeling. Next being a manager is not property of an employee. It's a relationship the employee has with the organization. You really need a course in basic data modeling. Then on top of that I see you're still doing assembly language programming with bit flags. We don't do that in SQL anymore. Doesn't it bother you that in violation of the law of identity and all common sense, your "id" can magically change from a customer to an employee to an automobile to a squid. The law of identity is "to be is to be something in particular"; there are no generic identities. This is one of the foundations of all Western thought and you don't know it.
CREATE TABLE Personnel
(emp_id CHAR(10) NOT NULL PRIMARY KEY,
emp_name VARCHAR(50));
See how a table has columns that represent ONLY the attributes of the entities modeled by that table? No extraneous stuff. I'm also curious about a 50 character name but I'm sure that was the result of careful research and not just a number you pulled out from having been an Access programmer at one point in your life and going to their default. United States Postal Service uses 35, but I'm sure they didn't do your careful research
CREATE TABLE Job_Assignments
(job_title VARCHAR(20) NOT NULL
REFERENCES Org_Chart (job_title),
emp_id CHAR(10) NOT NULL
REFERENCES Personnel(emp_id)
ON DELETE CASCADE,
..);
This table models a relationship between the personnel we've got in the jobs we've got in the organizational chart. You need to learn the difference between a relationship and an attribute.
CREATE TABLE Teams
(team_name CHAR(10) NOT NULL PRIMARY KEY,
emp_id CHAR(1) NOT NULL
REFERENCES Personnel(emp_id),
team_role CHAR() NOT NULL
CHECK (team_role IN ('member', 'manager')
);
Since I don't know how many IT managers you can have, I'll leave it at this however if there is only one manager per team, then we can use a WITH CHECK constraint to enforce uniqueness. Or make it mandatory. This is a little advanced, so so I won't post it. Also what the customers have to do with any of this? My guess is there some kind of relationship between the customers and the team
CREATE TABLE Customers
(cust_id CHAR(10) NOT NULL PRIMARY KEY,
cust_name VARCHAR(50) NOT NULL);
CREATE TABLE Customer_Assignments
(cust_id CHAR(10) NOT NULL
REFERENCES Customers (cust_id)
ON DELETE CASCADE,
team_name CHAR(10) NOT NULL
REFERENCES Teams(team_name)
ON DELETE CASCADE,
-- PRIMARY KEY (team_name, cust_id)
-- PRIMARY KEY (team_name)
-- PRIMARY KEY (cust_id)
);
You now need to make a design decision is a customer have an only one team assigned to him, multiple teams or does a team have multiple customers? This is expressed as a cardinality of a relationship – (1:1), (1:m) or (n:m)
>> As you can see, the table contains a record [sic: a row is nothing whatsoever like a record. This is a fundamental concept] for each member of the team, excluding the manger. The expected result is a list of all team members including their manager - for any given employee. Hence, I have to "produce" one record [sic] somehow. <<
I think that you went to the adjacency list model and recursion because you hadn't thought through all of the relationships. Been doing this for a few decades now and I've seen it before; recursion is not needed.
Please post DDL and follow ANSI/ISO standards when asking for help.
September 3, 2019 at 5:22 pm
Thanks guys for the great advice 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply