Search code examples
sqlsqlitegroup-byhaving

SQL query to find all UUIDs with a logical expression of flags set

I have a SQLite database with ~30 million rows with UUIDs and a flag column (among other columns) and I want to return all UUIDs that satisfy a logical expression of the flags.

  UUID | flag | ...
   1   | "a"  |
   1   | "b"  |
   1   | "a"  |
   2   | "b"  |
   2   | "c"  | 
   3   | "a"  |

For example I want to return all UUIDs that have flag ("a" AND ("b" or "c")) over all rows. In the above table only UUID=1 satisfies that constraint.

This is a similar question but it only asks about the case of having all 4 flags set --- there is no disjunction --- so the solutions there don't work here.

edit: @forpas HAVING SUM solution is what I was looking for but I ending up solving the problem by creating a user defined aggregate function before I saw it.

Solution

  • You can use aggregation with the conditions in the HAVING clause:

    SELECT UUID
FROM tablename
GROUP BY UUID
HAVING SUM(flag = 'a') > 0
   AND SUM(flag IN ('b', 'c')) > 0;

    You can add as many conditions as you want.

    See the demo.