A nested Select?
Hi I'm very new to sql, but I was given a job where I need to query the db (MS SQL 2005). I need to return all workers where the HeadID is listed (tables below) So I need all managers to match the HeadID and then all workers that match those managers using the ManagerID. How should I do it? Any help or any sql terminology that would help me find a better solution would be much appreciated. Thanks to
tb_Head: HeadID
tb_Manager: ManagerID, HeadID,
tb_Worker: WorkerID, ManagerID,
source to share
Use general table expression
USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
GO
source to share
It sounds like you want to use a recursive CTE. an online article book talks about your scenario. Here is an example of a set of code I just used in another stackoverflow article ...
CREATE TABLE dbo.ctetest (employeeid int primary key not null, managerid int null);
INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 1, NULL;
INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 2, 1;
INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 3, 1;
INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 4, 2;
INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 5, 2;
INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 6, 3;
INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 7, 2;
INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 8, 5;
INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 9, 4;
INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 10, 6;
INSERT INTO dbo.ctetest (employeeid, managerid) SELECT 11, 6;
WITH recursivecte (employeeid, managerid, level)
AS
(SELECT employeeid
, managerid
, 'level' = 0
FROM dbo.ctetest
WHERE managerid IS NULL
UNION ALL
SELECT ct.employeeid
, ct.managerid
, 'level' = rc.level + 1
FROM dbo.ctetest ct
JOIN recursivecte rc
ON ct.managerid = rc.employeeid)
SELECT *
FROM recursivecte rc
This should give you a hierarchy of each employee from level to level. If you want to return information about the next maximum level such as the name of the manager, you just need to add rc.managername to the second part of UNION ALL, add columns to the CTE table (that's WITH recursivecte (employeeid, managerid), level), and give the seat owners in the first part of the report.
source to share