0

Is there a way to compare bitmasks in the h2 database, similar to what has been asked in Comparing two bitmasks in SQL to see if any of the bits match ?

Having a table of users with different roles, I'd like to select all users that are programmers.

  User Table
  ----------
  ID  Username  Roles
  1   Dave      6
  2   Charlie   2
  3   Susan     4
  4   Nick      1

  Roles Table
  -----------
  ID  Role
  1   Admin
  2   Programmer
  4   Designer

The select should be something like 

SELECT * FROM UserTable WHERE Roles & 2 != 0

I know there is a BIT_AND function in h2 but do not know how to use it.

Community
  • 1
  • 1
Mikael Johansson
  • 59
  • 1
  • 5
  • In general, I'd recommend a `UserRoles` table that links users to roles using as many rows as necessary, rather than encoding data and then having to work out how to decode it. – Damien_The_Unbeliever Jul 19 '16 at 06:21

1 Answers1

0

I was confused by the BITAND and BIT_AND functions. The select statement should look like

SELECT * FROM UserTable WHERE BITAND(Roles, 2) != 0
Mikael Johansson
  • 59
  • 1
  • 5