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;

add some Example using table row and what result you expecting ,will help to get more appropriate answer — Shekhar Pankaj, Oct 09 '15 at 06:54
I think you have to use the `WITH` clause for a recursive query. Can you post some query to create the tables and some sample data so it's easier to help and totally clear what you need? So the subordinates of the manager of a department are all the non-manager people of that department plus all people from child departments, including their managers? — Andrew, Oct 09 '15 at 06:59

score 1 · Accepted Answer · edited May 23 '17 at 12:14

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.

edited May 23 '17 at 12:14

Community

1
1

answered Oct 09 '15 at 07:01

Juan Ruiz de Castilla

964
14
25

Hello, thanks for reply a tried your solution but there is one mistake. Your solution do not return people of child department if parent department have 0 people. How to solve this ? – Martin Haščák Oct 23 '15 at 08:40
No, i just tried my solution and it works in the scenary that you say, but remember that only list NO manager people (in other words only subordinates). check this http://sqlfiddle.com/#!3/2e5df/1 – Juan Ruiz de Castilla Oct 23 '15 at 15:11
Yor solution work, i updated code, to work as I want to.; `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;` – Martin Haščák Oct 24 '15 at 08:02

SQL hiearchy select query between two tables

1 Answers1