In a recent post I wrote about a flexible structure used for storing and retrieving classifications form a database. My example retrieved “entities” for a single classification, and those entities that matched any of that classifications children.

In this entry I want to expand on this approach, and demonstrate how results can be limited to only those entities that match a specific list of classifications.

Using the tables above, let retrieve all entities that are related to ClassificationId 9 (Times New Roman – Bold) AND ClassificationId 4 (Blue).

At first glance I thought this was pretty simple and used the IN clause to match my list of classification ids.

1
2
3
4

SELECT * FROM Entity 
LEFT JOIN EntryClassification 
ON Entity.Id = EntryClassification.EntityId 
WHERE EntryClassification.ClassificationId in (9,4)

Unfortunately this approach fails to return the correct result set.  Instead of retrieving those entities that are related to BOTH classifications, the IN clause determines whether a specified value matches ANY value in the list. 

After quite a few experimentations , here is the solution I came up with.

1
2
3
4
5

SELECT * FROM Entity WHERE Entity.Id in 
(SELECT ec.EntityId FROM EntityClassification ec
WHERE ec.ClassificationId in (9,4) 
GROUP BY ec.EntryId 
HAVING Count(ec.EntryId) = 2))

If the number of items in your list will change, then you will need to either pass in the number of items or calculate the total by examining the string of Ids.  If you always know the delimiter, the following is an interesting hack to determine the number of items.

1
2
3

DECLARE @ClassificationIds varchar(10) = '9,4'
DECLARE @Count int
SET @Count = (LEN(@ClassificationIds ) - LEN(REPLACE(@ClassificationIds , ',', ''))+1)

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))

Today I was tasked with formating a SQL Datetime field as part of a FOR XML query.  More often then not, I can get away with formating in the presentation layer and leave all dates in the standard ISO8500.  Today I needed a nice looking date to be concatenated within a business name.  I stumbled across this approach which uses the Convert method within SQL 2005.

• MON DD YYYY HH:MIAM (OR PM) — Feb 5 2003 5:54AM
  CONVERT(CHAR(19),GETDATE())
• MM-DD-YY FORMAT — 02-05-03
  CONVERT(CHAR(8),GETDATE(),10)
• MM-DD-YYYY FORMAT — 02-05-2003
  CONVERT(CHAR(10),GETDATE(),110)
• DD MON YYYY FORMAT — 05 Feb 2003
  CONVERT(CHAR(11),GETDATE(),106)
• DD MON YY FORMAT — 05 Feb 03
  CONVERT(CHAR(9),GETDATE(),6)
• DD MON YYYY HH:MM:SS:MMM(24H) — 05 Feb 2003 05:54:39:567
  CONVERT(CHAR(24),GETDATE(),113)