Ask Ben: Querying Groups With Conditional Elements

I hav a situation in my academic project, and was struggling to get a proper approach towards the solution, then i decided to ask 'kind-hearted' ppl for help ... while browsing net for forums wid posts with similar situation, i 'stumbled' upon you site. it's a nice and great job u r doin here ben. thanks a ton... do consider to direct me towards the solution for the following situation.

The following table reflects the One-to-Many relationship between TransID and TagID

--------------------
|TransID | TagID |
--------------------
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 3 |
| 3 | 4 |
--------------------

i would need to know how exactly i should be aproaching the problem to find out solution for following situations:

STRICT MODE:
------------

* TransID with TagID (1 AND 4 AND Not 2)

Result : i should get only TransID:3 as the result. (ALL Conditions shud return true, so in the interface the user would have selected Having 1,4 and not having 2).

Relaxed Mode:
-------------

* TransID with TagID (1 OR 4 OR Not 2)

Result : i should get TransID:1,2,3 as the result, since each of the TransID satisfies one or other condition. (ANY Condition returning true the TransID qualifies to be returned, and in the interface the user would hav selected Having 1,4 and not Having 2)

Before we get into this, let's start out by building a SQL temp table into which I can insert the test data:

Now that we have that in place, our @data table should mimic what the user has described (although I have changed the column names to line up more appropriately with my own database naming conventions).

Ok, so let's take a look at the problem. For the Strict mode part, the first thing that jumps to mind is grouping. We want to check out the group of TransIDs and make sure that each group contains either TagID 1 or 4, but definitely not TagID 2. To examine groups in SQL Server, we can use the GROUP BY clause. Then, to examine the contents of each group, we can leverage the fact that the COUNT() aggregate can handle CASE statements. If you remember my exploration of the COUNT() / CASE scenario, you will remember that the COUNT() aggregate does not include NULL values in its tally. Therefore, if we can conditionally include values, we can use the COUNT() of each group to narrow down the selection.

Taking this understanding and applying it to the problem at hand, we want to make sure that each TransID group has at least one count of the TagID 1 or 4 and a zero count of the TagID 2:

Running this SQL, we get the following output:

TransID: 3

This works, but it looks really complicated. We can start to simplify the query if we look at the problem in a slightly different way. We can think of the 1 and 4 requirements as filter and then the NOT 2 as a secondary layer of filter. Let's run a query that includes just the first part of that filtering:

This gives us the following output:

TransID: 1, 2, 3

Good, the first part works. Now, we want to apply the second filter, not having TagID 2. We can't just add another simple WHERE clause for != 2 since any TagID that is 1 or 4 is ALSO not equal to 2. What we need to do is apply the not 2 to each group of TransIDs. To do this, we are going to add a NOT EXISTS clause:

Running the above code, we get the following output:

TransID: 3

This version works as well. To me, this is somewhat easier to read than the leveraged COUNT() aggregate; however, I am not sure exactly which one is going to be faster. I am sure that it will depend somewhat on the size of the table and on the indexes that are in place.

Now that we have the Strict Matching out of the way, let's take a look at the Relaxed Matching mode. Relaxed matching is much easier as it doesn't really apply to the group level, but rather to the individual records being examined. As such, we can simply use a straightforward WHERE clause to limit each returned TransID and then group by the TransID to make sure we don't get duplicate values:

Running the above code, we get the following output:

TransID: 1, 2, 3

Works fine and as you can see, relaxed mode matching is much easier and faster to perform.

I hope that helps. Please feel free to follow up with comments below.