-1

How do you filter out a group in the HAVING clause if at least one row of the group meets some condition? For example, If have the following table:

enter image description here

And I want group by id1 and id2 and exclude any group where at least one of its rows has "value" in the someText column ? So in my results, the group (1,1) should be excluded but (1,2), (2,1) and (2,3) should be returned. Preferably, I am looking for a solution using the HAVING clause(this is just a simple example to illustrate the problem - but I am working on large query))

GMB
  • 216,147
  • 25
  • 84
  • 135
oneCoderToRuleThemAll
  • 834
  • 2
  • 12
  • 33

1 Answers1

1

You can use group by and having with a conditional expression:

select id1, id2
from mytable
group by id1, id2
having max(someText = 'value') = 0
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Can you explain how this works? Why does max work like this? – oneCoderToRuleThemAll Mar 23 '20 at 18:58
  • @oneCoderToRuleThemAll: in this context, `someText = 'value'` returns 1 if the condition is fullfilled, else 0. So `max((someText = 'value') = 0` means: there is no value in the group that satisfy the condition. – GMB Mar 23 '20 at 19:09
  • How does MySQL evaluate this? Does it iterate through all values of `someText` column for that group, apply this boolean expression to each one, pick out the maximum value(which would be 1 if the value is present) ? – oneCoderToRuleThemAll Mar 23 '20 at 19:13