I have a query that looks like this:
SELECT ordDetails.OrderId
FROM OrderDetails 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'"?
CodePudding user response:
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'
)
CodePudding user response:
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
CodePudding user response:
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
;