Query SQL Database so result will be as tree view

Question

I have razor page menu that has three level of drop-down values are saved in three tables in SQL Database and are linked with parent id

Table Directory directoryId, DirectoryName

Table Department departmentId, DepartmentName, DirectoryId

Table Sector sectorId, SectorName, DepartmentId

so the logic is that each directory has its own departments that have own sectors i need help how to query Tables so result would be in a way of tree view

Answer 1

Assuming you are using MSSQL, and in the absence of any sample data, I think the TSQL query could be:

WITH tree_view AS (
    SELECT directoryId, DirectoryName, CAST(DirectoryName AS VARCHAR(1024)) AS tree
    FROM Directory
    UNION ALL
    SELECT d.departmentId, d.DepartmentName, tree_view.tree + ' > ' + d.DepartmentName
    FROM Department d
    JOIN tree_view ON tree_view.directoryId = d.DirectoryId
    UNION ALL
    SELECT s.sectorId, s.SectorName, tree_view.tree + ' > ' + s.SectorName
    FROM Sector s
    JOIN tree_view ON tree_view.departmentId = s.DepartmentId
)
SELECT * FROM tree_view;

If you are not using MSSQL you can probably use double pipes || instead of plus symbols to perform the concatenations, or use a CONCAT() function.