Testing for existence using SELECT WHERE HAVING and NOT HAVING in a grouped subset
- by IanC
I have data on which I need to count +1 if a particular condition exists or another condition doesn't exist. I'm using SQL Server 2008.
I shred the following simplified sample XML into a temp table and validate it:
<product type="1">
<param type="1">
<item mode="0" weight="1" />
</param>
<param type="2">
<item mode="1" weight="1" />
<item mode="0" weight="0.1" />
</param>
<param type="3">
<item mode="1" weight="0.75" />
<item mode="1" weight="0.25" />
</param>
</product>
The validation in concern is the following rule:
For each product type, for each param type,
mode may be 0 & (1 || 2). In
other words, there may be a 0(s), but
then 1s or 2s are required, or there
may be only 1(s) or 2(s). There cannot
be only 0s, and there cannot be 1s
and 2s.
The only part I haven't figured out is how to detect if there are only 0s. This seems like a "not having" problem.
The validation code (for this part):
WITH t1 AS (
SELECT SUM(t.ParamWeight) AS S, COUNT(1) AS C,
t.ProductTypeID, t.ParamTypeID, t.Mode
FROM @t AS t
GROUP BY t.ProductTypeID, t.ParamTypeID, t.Mode
),
...
UNION ALL
SELECT TOP (1) 1 -- only mode 0 & (1 || 2) is allowed
FROM t1
WHERE t1.Mode IN (1, 2)
GROUP BY t1.ProductTypeID, t1.ParamTypeID
HAVING COUNT(1) > 1
UNION ALL
...
)
SELECT @C = COUNT(1)
FROM t2
This will show if any mode 1s & 2s are mixed, but not if the group contains only a 0. I'm sure there is a simple solution, but it's evading me right now.
EDIT:
I thought of a "cheat" that works perfectly. I added the following to the above:
SELECT TOP (1) 1 -- only mode 0 & (null || 1 || 2) is allowed
FROM t1
GROUP BY t1.ProductTypeID, t1.ParamTypeID
HAVING SUM(t1.Mode) = 0
However, I'd still like to know how to do this without cheating.
