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

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.

CodePudding user response：

Consider the following approach using min() and max() functions:

select UUID
from Tbl
group by UUID having min(flag)="a" and min(flag)<>max(flag)

When min() is used with strings, the strings are ordered alphabetically A-Z and the first one is returned. In your case flags with a value will be the minimum and flags with c value will be the maximum. The having clause will ensure that there's an a value within the group and that value is not equal to the maximum flag value of the group (in this case b or c).

See a demo from here.

CodePudding user response：

you can use EXISTS if there are other rows with the same UUID, and the flag

SELECT
    UUID 
FROM
    mytable m
WHERE
    flag = 'a'
AND
    (EXISTS (
        SELECT 
            1 
        FROM 
            mytable
        WHERE 
           flag = 'b'
           and UUID = m.UUID

    ) OR
     EXISTS (
        SELECT 
            1 
        FROM 
            mytable
        WHERE 
           flag = 'c'
           and UUID = m.UUID
        )
    )

or you can simpler write

SELECT
    UUID 
FROM
    mytable m
WHERE
    flag = 'a'
AND
    EXISTS (
        SELECT 
            1 
        FROM 
            mytable
        WHERE 
           flag IN ('b','c')
           and UUID = m.UUID

    )