Frequently our team is tasked with creating a way to categorize data. One very common and flexible approach is to create a table which contains a series of parent-child relationships. This hierarchical approach can be extremely flexible and efficient for categorizing multiple levels of content. Here is an example of a table used to store this type of classification.

From this table we can visualize a simple nested tree of classifications

• Attributes
  • Color
    • Red
    • Blue
    • Green
    • Purple
  • Font
    • Times New Roman
      • Bold
      • Italic
    • Arial
      • Bold
      • Calibri

From a SQL perspective, interacting with this structure is quite painless. Through the use of a composite table, this structure lends itself well to having entities with multiple classification.

If a user wants entities which are classified as “Times New Roman” “Bold” they restrict the results to those with a ClassificationId = 9.

Now what if they want are those entities which have any of the “Font” sub classifications (ClassificationId = 7,8,9,10,11,12,13,14)?

This is where it gets a bit more tricky. The approach I have found most flexible is to create a custom table-valued function which recursively walks the table and returns all children of the specified parent.  The results of this reqursive query are returned in the form of a temporary named result set, known as a common table expression (CTE). If our desire was to return more than just the classifications Id we could use this CTE as a sub query on an additional query.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

ALTER FUNCTION [Resource].[ClassificationChildren]
(
 @ClassificationId int
)
RETURNS TABLE
AS
RETURN
( WITH Classes (Id, ParentId, [Level]) AS
 (
  -- Create the anchor query. This establishes the starting point
  SELECT c.[Id],c.[ParentId],0
  FROM Classification c
  WHERE c.[Id] = @CategoryId
  UNION ALL
  -- Create the recursive query. This query will be executed
  -- until it returns no more rows
  SELECT
  c.[Id],c.[ParentId], b.[Level]+1
  FROM Classification c
  INNER JOIN Classes b ON COALESCE(c.[ParentId],0) = b.[Id]
 )
SELECT k.Id FROM Classes k
)

Once I have this function, I can create a query to give me the results I desire.

1
2
3
4

SELECT * FROM Entity LEFT JOIN 
EntryClassification ON Entity.Id = EntryClassification.EntityId
WHERE EntryClassification.ClassificationId in 
(SELECT Id FROM ClassificationChildren(7))