'Is there a way to check that none of the rows in a grouping contain a value?

I have a query that looks like this:

SELECT  ordDetails.OrderId
FROM    orders.OrderDetail ordDetails
WHERE   ordDetails.Class <> 'O'
GROUP BY ordDetails.OrderId

But this does not work quite right. OrderId is not unique in this table (hence the group by clause). My where clause only removes OrderIds that have ALL rows with a Class of 'O'. (If even one row has a value other than 'O', then that OrderId is included in the result.)

I want all the OrderIds that have NO rows with a value of 'O'.

Normally I would just use an aggregate function like MAX or MIN. But alas, MAX returns 'R' and MIN returns '' (empty string).

What I really need is an aggregate function that checks that none of the values in the aggregate match a parameter. Unfortunately there is no such aggregate function.

Is there a way to say (in a group by query) "only give me results that have no rows matching an 'O'"?

sqlsql-servertsqlgroup-bysql-server-2012


Solution 1:[1]

I think you are looking for something like this:

select distinct
   o.OrderId
from
   OrderDetails as o
where not exists
(
   select 1
   from
      OrderDetails as o2
   where
      o2.OrderId = o.OrderId and
      o2.Class = '0'
)

Solution 2:[2]

You could also use:

SELECT  ordDetails.OrderId
FROM    OrderDetails ordDetails
GROUP BY ordDetails.OrderId
HAVING sum(case when ordDetails.Class='O' then 1 else 0 end)=0

Solution 3:[3]

And more options. First using the except set operator

select distinct OrderId from @OrderDetails
except
select OrderId  from @OrderDetails where Class = 'O'
;

And conditional counting with HAVING clause

select Orderid 
from @OrderDetails
group by OrderId
having count(case Class when 'O' then 1 else null end) = 0
;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Aducci
Solution 2 tinazmu
Solution 3 SMor

Related Questions