Nested Select statement??

Question

Hi I'm very new to sql but have been passed a job in which I need to query the db(MS SQL 2005) I need to return all workers where a HeadID is given.(tables below) So I need to get all the managers that match the HeadID and then all the workers that match those managers by ManagerID. How would I do this? Any help or any sql terminology that would help me better search for the solution would be much appreciated. Thanks

tb_Head: HeadID

tb_Manager: ManagerID, HeadID,

tb_Worker: WorkerID, ManagerID,

Answer 1

Use common 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

Answer 2

A simple way would be to do something like this:

select * from tb_Worker
  join tb_Manager on tb_Worker.ManagerID = tb_Manager.ManagerID
  join tb_Head on tb_Manager.HeadID = Head.HeadID
  where tb_Head.HeadID = <given value>

Tune your table names and select columns as appropriate.

Answer 3

Sounds like you want to use a recursive CTE. The books online article talks about your kind of scenario. Here's a sample set of code that I just used in a different 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 the hierarchy of each employee from level to level. If you want to return information about the next highest level such as a manager name, then you just need to add rc.managername to the second part of the UNION ALL, add columns to the CTE table (that's the WITH recursivecte (employeeid, managerid, level) section, and give place-holders in the first portion of the statement.