SQL hiearchy select query between two tables

Question

I have 2 tables (People, Department).

Department table look like this:

ID (int)
Name (varchar)
ParentID (int)

People table like this:

Id (int)
FirstName (varchar)
SureName (varchar)
DepartmentId (int)
Manager (Bool)
Asistant (Bool)

I need to create query, which select all direct subordinates of concrete person which is manager of concrete depertment. And one query which select all not only direct subordinates of manager. Each department have one manager and one asistant. The peoples with manager == false are direct subordinates, and peoples in child depertment are subordinates of parent depratment .

I have no idea how to crete this queries in SQL / LINQ.

I will be grateful for any help!

Example: DEPARTMENT

Id   Name          Parent
0    Department1   null
1    Department2   0
2    Department3   1

Example:

People
ID    Name      Department    DepartmentId    Manager    Asistant
1     Martin    Joshua            0             1           0
2     Ondra     Joshua2           0             0           0
3     Petr      Joshua3           0             0           0
4     Todd      Joshua3           1             1           0
5     Alex      Joshua3           1             0           0
6     Iva       Joshua3           1             0           0
7     Otto      Joshua3           2             1           0
8     Todd      Joshua3           2             0           0

I need for exmple select all (not only direct) subordinates of manager in deparment with id 0, result wil look like:

2     Ondra     Joshua2           0             0           0
3     Petr      Joshua3           0             0           0
4     Todd      Joshua3           1             1           0
5     Alex      Joshua3           1             0           0
6     Iva       Joshua3           1             0           0
7     Otto      Joshua3           2             1           0
8     Todd      Joshua3           2             0           0

I´m not sure direct subordinates SQL query:

SELECT * FROM dbo.PeopleView WHERE DepartmentId = 162 AND Manager = 0; -- all direct s (162)

SOLUTION:

;WITH CTE AS
(
    SELECT 1 as EMPLEVEL, H1.Id, H1.ParentId, H1.Name FROM DepartmentView H1 WHERE Id = 6
    UNION ALL
    SELECT EMPLEVEL + 1, H2.Id, H2.ParentId, H2.Name FROM DepartmentView H2
    INNER JOIN CTE ON H2.ParentId = CTE.Id
)
SELECT DISTINCT P.Id, P.LastName,P.FirstName,P.DepartmentId,P.Manager,P.Assistant FROM CTE as T JOIN PeopleView as P on T.Id = P.DepartmentId;

Answer 1

In adition to your first query, and like a comment from @Andrew says, a recursive query can help with this:

;WITH CTE
AS
(
SELECT ID,FirstName,SureName,DepartmentID,Manager,Assistant, 0 AS EMPLEVEL  FROM PEOPLE A  WHERE DepartmentId = 1 AND Manager = 0
UNION ALL 
SELECT B.ID,B.FirstName,B.SureName,B.DepartmentID,B.Manager,B.Assistant,EMPLEVEL +1 FROM PEOPLE B 
INNER JOIN DEPARTMENT D
ON B.DepartmentID = D.ID
INNER JOIN CTE 
ON D.ParentID = CTE.DepartmentID 
)
SELECT DISTINCT * FROM CTE 

And for convert to LINQ, you can read this post :P: Common Table Expression (CTE) in linq-to-sql?

Hope this help, best regards.