Search code examples
mysqlgroup-byhaving

only return the group key where no row of the group is matching

| target | start | end  |
| ------ | ----- | ---- |
| 1      | NULL  | NULL |
| 1      | 100   | NULL |
| 2      | NULL  | NULL |
| 2      | NULL  | 100  |

I am trying to run a query over the described table, which gives me all target values where all of the connected row does not match start IS NOT NULL and end IS NULL

So the expected return would be

| target |
| ------ |
| 2      | <-- Has no connected rows where start IS NOT NULL and end IS NULL

I have already tried a self join, which did not accomplish the wanted result

Solution

  • Use aggregation and the condition in the HAVING clause:

    SELECT target
FROM tablename
GROUP BY target
HAVING SUM(start IS NOT NULL AND end IS NULL) = 0;

    See the demo.