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

by

[ad_1]

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

[ad_2]

solved Find combination which does not have ‘AC’ status