Thanks, all, for the kind words!
jcrawf02 (9/8/2010)
Hugo, do you have an application of this?
Yes, I have. Although that particular situation called for a HAVING NOT EXISTS.
I can't share the actual details, so I'll have to pretend it was about an order processing system. The requirement was to find those orders from a particular customer where no other order on the same date (but by any customer) had the same number of order items. (I know this requirement makes no sense at all; just trust me that it does make sense in the actual context, which I can't disclose).
This is how I did it:
SELECT o.OrderNo, o.OrderDate,
SUM(oi.Amount) AS TotalOrderAmount
FROM Orders AS o
INNER JOIN OrderItems AS oi
ON oi.OrderNo = o.OrderNo
WHERE o.CustomerNo = @CustomerNo
GROUP BY o.OrderNo, o.OrderDate
HAVING NOT EXISTS
(SELECT *
FROM Orders AS o2
INNER JOIN OrderItems AS oi2
ON oi2.OrderNo = o2.OrderNo
WHERE o2.OrderNo <> o.OrderNo
AND o2.OrderDate = o.OrderDate
GROUP BY o2.OrderNo
HAVING COUNT(oi2.ItemCode) <> COUNT(oi.ItemCode));
I could also have solved this in a different way, by changing the HAVING clause of the outermost to
HAVING COUNT(oi.ItemCode) <> ALL
(SELECT COUNT(oi2.ItemCode)
FROM Orders AS o2
INNER JOIN OrderItems AS oi2
ON oi2.OrderNo = o2.OrderNo
WHERE o2.OrderNo <> o.OrderNo
AND o2.OrderDate = o.OrderDate
GROUP BY o2.OrderNo);
But frankly, I believe the ALL operator to be even more obscure than HAVING EXISTS. 😀
(EDIT: Proved my own point by using ANY instead of ALL when I posted this - now corrected)