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)