CODEWITHSUNDEEP
sql-serverfiltertreecommon-table-expression
user1699353
user1699353

Reputation: 33

MSSQL CTE tree with filter how to?

There's a table that describes object hierarchy and their type:

DECLARE @TTable TABLE
(
 ID int
 ,Higher int -- for some reason this is how ID_PARENT named
 ,[Type] int
)

I need to select a subtree with branches only containing nodes of certain [Type]. Since I'm a beginner at SQL generally and CTE I did this by first finding all the branches that contain objects of certain type from bottom to top:

;WITH c
AS
(
SELECT 
    ID
    ,Higher
    ,[Type]
FROM
    dbo.EnObj
WHERE Type = 40

UNION ALL

SELECT
    t.ID
    ,t.Higher
    ,t.Type
FROM
    dbo.EnObj T
INNER JOIN c ON t.ID = c.Higher
)

Then chose unique records into a table variable:

INSERT @TTable
SELECT DISTINCT
c.ID
,c.Higher
,c.Type
,c.Name
,c.Voltage
FROM  c

And then built an hierarchy by CTE with sorting from top to bottom:

;WITH CTE AS (
    SELECT
    ID
    ,Higher
    ,[Type]
,CAST(ID AS VARCHAR(128)) AS Sort
FROM    
    @TTable
WHERE
    Higher IS NULL
UNION ALL
SELECT
    N.ID
    ,N.Higher
    ,N.[Type]
    ,CAST(P.Sort + '/' + CAST(N.ID AS VARCHAR) AS VARCHAR(128))
FROM    
    @TTable N
INNER JOIN CTE AS P ON P.ID = N.Higher
)
SELECT 
    ID
    ,Higher
    ,[Type]
    ,Sort
FROM CTE 
ORDER BY Sort

It works, but I wonder if there's a way to optimize this query, like using one CTE instead of two or have a filter in CTE.

Upvotes: 1

Views: 857

Answers (1)

arjthakur
arjthakur

Reputation: 61

with Hierachy 
as  
(
    select ID, Higher, Type from EnObj
        WHERE Type = 40 -- Whatever your search criteria

        union all   
    select EnObj.ID, EnObj.Higher ,EnObj.Type from EnObj    
    inner join Hierachy 
    on tblOrgLevel.Higher = Hierachy.ID
 )
select * from Hierachy --where ID <> 40

Another solution

WITH DirectReports
AS 
(
    SELECT parentid, id, Title, 0 AS EmployeeLevel
    FROM dbo.tblOrgLevel  
    WHERE ISNULL(parentid ,0) = 0 and  FKAgencyID = 58
    UNION ALL
    SELECT e.parentid, e.id, e.Title, EmployeeLevel + 1
    FROM dbo.tblOrgLevel AS e
        INNER JOIN DirectReports AS d
        ON e.parentid= d.id 
)
SELECT *
FROM DirectReports
ORDER BY parentid;
GO

You can ask me for more clarification, and some modifications

Upvotes: 1

Related Questions