I face a tricky query (for my level obviously ^^), I explain my issue: I have a column A with service name, and a column B with state of these services. I have same services deployed on mulitple hosts but with different state currently. Here a sample of my DB

What I'm looking for is to display only services where all rows related to this service is NOK, if one serviceX is in OK state I want to exclude it from output. In my example I would like to retreive only ServiceB, because there is no row where ServiceB is on another state that NOK in whole table.

Thx for your help

With a : SELECT service,state,count(*) FROM table GROUP BY service,state

I'm able to retrieve a counter for each different value

That's quite easy with "WHERE state != 'NOK'" condition to discard service in OK state, but it falsed the result as serviceA will appear whereas one row is not 'NOK'.

CodePudding user response：

Try this:

SELECT service, state, count(*) FROM table WHERE state = 'NOK' GROUP BY service, state;

CodePudding user response：

You can use in-subquery for that:

"Select all distinct services which do not belong to a set where a service has OK state"

select distinct Service
from t
where Service not in (
  select Service
  from t
  where state='OK'
);

CodePudding user response：

There are different ways that you can do this, in my opinion one of the easiest is with a subquery. You want services where all hosts are NOK that is the same to say that you want services where none of the hosts are OK, right? That is easier to find: all services where at least one service is OK:

SELECT DISTINCT service
FROM services
WHERE state = 'OK'

Those are the services you don't want, so to get the ones you do want (the rest), you can just select all services not returned by that query:

SELECT DISTINCT service from services where service not in (
  SELECT DISTINCT service
  FROM services
  WHERE state = 'OK' )

SQL fiddle: http://sqlfiddle.com/#!9/6c452a/9/0

Hope that helps