I need help with a problem I am facing processing hierarchical data.

Schema of the tables that maintain hierarchical data:

Category table:

|   ID  |   Label | 

Mapping table:

|   ID  |   QualifierID     | ItemID    | ParentID  |

Step 1: Wrote a simple self-join query to trasnform above mappings:

WITH category_masterlist AS (
  SELECT    id,
            label
  FROM Category
)

select id, id as itemid, label, NULL as parentId from [Category] where categoryLevel = 1
UNION
select itemid as id, itemId, (select label from category_masterlist where id = cm.itemid) Label, parentId
from [CategoryMapping] cm

Step 2: Wrote a self-join query using common table expression to return mapping data as follows:

WITH CategoryCTE(ParentID, ID, Label, CategoryLevel) AS
(
    SELECT ParentID, ItemID, Label, 0 AS CategoryLevel
    FROM [view_TreeviewCategoryMapping]
    WHERE ParentID IS NULL
    UNION ALL
    SELECT e.ParentID, e.ItemID, e.Label, CategoryLevel   1
    FROM [view_TreeviewCategoryMapping] AS e
        INNER JOIN CategoryCTE AS d
        ON e.ParentID = d.ID
)
SELECT distinct ParentID, ID, Label, CategoryLevel
FROM CategoryCTE

|   ID  |   Label           | ParentID  | CategoryLevel |
--------------------------------------------------------------------------------                                
|   90  |   Satellite       |   NULL    |   0       |
|   91  |   Concrete        |   NULL    |   0       |
|   92  |   ETC             |   NULL    |   0       |
|   93  |   Chisel          |   NULL    |   0       |
|   94  |   Steel           |   NULL    |   0       |
|   96  |   Wood            |   NULL    |   0       |
|   97  |   MIC Systems     |   90      |   1       |
|   97  |   MIC Systems     |   91      |   1       |
|   99  |   Foundations     |   91      |   1       |
|   100 |   Down Systems    |   91      |   1       |
|   101 |   Side Systems    |   91      |   1       |
|   102 |   Systems         |   91      |   1       |
|   98  |   DWG             |   92      |   1       |   
|   97  |   MIC Systems     |   93      |   1       |
|   97  |   MIC Systems     |   94      |   1       |
|   99  |   Foundations     |   94      |   1       |   
|   100 |   Down Systems    |   94      |   1       |
|   101 |   Side Systems    |   94      |   1       |
|   102 |   Systems         |   94      |   1       |
|   97  |   MIC Systems     |   95      |   1       |
|   98  |   DWG             |   95      |   1       |
|   102 |   Systems         |   95      |   1       |
|   103 |   Project Management| 95      |   1       |   
|   104 |   Software        |   95      |   1       |
|   99  |   Foundations     |   96      |   1       |
|   119 |   Fronts          |   97      |   2       |
|   121 |   Technology      |   98      |   2       |
|   112 |   Root Systems    |   98      |   2       |   
|   112 |   Root Systems    |   99      |   2       |
|   137 |   Closed Systems  |   112     |   3       |
|   203 |   Support         |   121     |   3       |

Step 3: I would like to filter above results so that only categories that are mapped completely are returned. Completed mapping is a mapping that has children at level=3. For example, below is what I am looking for based on above resultset:

|   ID  |   Label           | ParentID  | CategoryLevel |
--------------------------------------------------------------------------------

|   96  |   Wood            |   NULL    |   0       |
|   92  |   ETC             |   NULL    |   0       |
|   98  |   DWG             |   92      |   1       |
|   99  |   Foundations     |   96      |   1       |
|   121 |   Technology      |   98      |   2       |
|   112 |   Root Systems    |   98      |   2       |
|   112 |   Root Systems    |   99      |   2       |
|   137 |   Closed Systems  |   112     |   3       |
|   203 |   Support         |   121     |   3       |

Step 4: Ultimately, end user should be presented with a tree view control as follows:

Root
|
|---Wood
|   |---Foundations
|       |---Root Systems
|           |---Closed Systems
|
|---ETC
|   |---DWG
|       |---Technology
|           |---Support
|       |---Root Systems
|           |---Closed Systems

Please note, a category can have multiple parents. For example, Root Systems has two parents - DWG and Foundations. Did I get the schema correct for category and mapping table especially for the case when a category can have multiple parents?

How can I filter out categories that are not mapped completely from Step 2 to Step 3? That is the hurdle I am unable to cross. Any pointers? I can filter them out at the application level but would really love to filter them out at database level.

I am open to suggestions and recommendations that will help me achieve my goal. I also want a confirmation that the schema I am using is the most efficient one.

Thank you!

CodePudding user response：

Here is a working option that uses the datatype hierarchyID

The nesting is option and really for illustration.

Example

Declare @Top   int         = null      --<<  Sets top of Hier Try 94
 
;with cteP as (
      Select ID
            ,ParentID 
            ,Label 
            ,HierID = convert(hierarchyid,concat('/',ID,'/'))
      From   YourTable 
      Where  IsNull(@Top,-1) = case when @Top is null then isnull(ParentID ,-1) else ID end
      Union  All
      Select ID  = r.ID
            ,Pt  = r.ParentID 
            ,Label   = r.Label
            ,HierID = convert(hierarchyid,concat(p.HierID.ToString(),r.ID,'/'))
      From   YourTable r
      Join   cteP p on r.ParentID  = p.ID)
Select Lvl   = HierID.GetLevel()
      ,ID
      ,ParentID
      ,Label  = replicate('|----',HierID.GetLevel()-1)   Label  -- Nesting Optional ... For Presentation
      ,HierID_String = HierID.ToString()
 From cteP A
 Order By A.HierID

Results

Now if @Top was set to 94