SQL for each row do something

Question

I have a SQL query that finds the "departments" the specified person has access to.

And some departments are subdepartments of others, so when the person has acces to a department he has also access to all of his subdepartments.

This works because in the departments table, every department has a name and ID and a "fatherID" which specifies what department is his father department.

But it is also possible that this nests even further and i found no way to show the names of all departments below 1 level of subdepartments.

SELECT B.DepartmentDesc FROM  CompanyDepartmentPermission A 
INNER JOIN CompanyDepartment] B 
ON A.DepartmentID=B.DepartmentID 
OR A.DepartmentID=B.FatherDepartmentID 
WHERE A.PEmplID = 68

This is possible. I get all the names from the department and all his subdepartments:

But in this case i get every subdepartment, except the on nested a level lower.

My goal is to be able to recursively also show all nested subdepartments. And i HAVE to do this in only 1 query. As i am very inexperienced and have not much insight in nested querys i have not found a way to solve this. I hope someone can give me a nudge to the right direction.

PS: The pictures where from a C# program i made where i made it work and this is just for clarification how it looks. And i hope i could explain what i want. Otherwise let me know and i will try to rephrase this :)

EDIT: This is de structure of the two tables. Thanks Akaino for pointing out

SOLUTION: Sadly none of the asnwers here helped me, but instead a halfway similar question here on SO. The WITH UNION statements helped me a lot. This is The working query for my problem.

WITH DepartmentHirarchy AS
(
SELECT B.DepartmentID,DepartmentDesc,FatherDepartmentID, DepartmentResponsiblePEmplID
FROM CompanyDepartment AS B
INNER JOIN CompanyDepartmentPermission AS A ON A.DepartmentID=B.DepartmentID
WHERE A.PEmplID= 34 OR DepartmentResponsiblePEmplID = 34
UNION ALL
SELECT  nextLevel.DepartmentID,nextLevel.DepartmentDesc,nextLevel.FatherDepartmentID, nextLevel.DepartmentResponsiblePEmplID
FROM DepartmentHirarchy AS recCall
INNER JOIN CompanyDepartment AS nextLevel ON nextLevel.FatherDepartmentID=recCall.DepartmentID
)
SELECT * FROM DepartmentHirarchy
ORDER BY FatherDepartmentID,DepartmentID
GO

Answer 1

For parent/child tree hierarchy, try the following SQL code.

I assume, your table name is Departments. You can change the table name in the following SQL.

I works with SQL Server.

WITH RELATION (DepartmentID, FatherDepartmentID, DepartmentDesc, [Level], [Order])
AS
(
    SELECT DepartmentID, FatherDepartmentID, DepartmentDesc, 0, CAST(DepartmentID AS VARCHAR(200))
    FROM Departments
    WHERE FatherDepartmentID IS NULL
    UNION ALL
    SELECT P.DepartmentID, P.FatherDepartmentID, P.DepartmentDesc, R.[Level]+1, CAST(R.[Order] + '_' + CAST(p.DepartmentID AS VARCHAR(200)) AS VARCHAR(200))  
    FROM Departments P
    JOIN RELATION R ON P.FatherDepartmentID = R.DepartmentID
)
SELECT RIGHT('----------', ([Level]*3)) + DepartmentDesc  
FROM RELATION R
JOIN CompanyDepartment D ON D.DepartmentID = R.DepartmentID 
WHERE PEmplID= 68
ORDER BY R.[ORDER]

Answer 2

It seems like you want to do an hierarchy search. If your server supports the use of the hierarchy datatype (if not, you might want to consider it if you are planning to use a lot of hierachy searches) you could try and use the isDescendantOf method.

Example

DECLARE @parent hierarchyid
SELECT @parent = B.DepartmentDesc
FROM CompanyDepartment B 
Where B.DepartmentDesc = 'Name your deparment'

SELECT *  
FROM CompanyDepartment 
WHERE DepartmentDesc.IsDescendantOf(@parent) = 1 ;