[Solved] Find combination which does not have ‘AC’ status

by


There are several query patterns that will achieve that result.

An example of one of those patterns, using an GROUP BY operation, with aggregation of a condition (to exclude the groupings where there’s a row in the grouping that has a status value of 'AC')

  SELECT t.id
       , t.state
       , t.group
    FROM mytable t
   GROUP
      BY t.id
       , t.state
       , t.group
  HAVING MAX(CASE WHEN t.status="AC" THEN 1 ELSE 0 END) = 0

An example of another pattern, using an anti-join:

  SELECT t.id
       , t.state
       , t.group
    FROM mytable t
    LEFT
    JOIN mytable s
      ON s.id    = t.id
     AND s.state = t.state
     AND s.group = t.group
     AND s.status="AC"
   WHERE s.id IS NULL
   GROUP
      BY t.id
       , t.state
       , t.group

1

solved Find combination which does not have ‘AC’ status