Join tables with enum values

Question

This might be the follow up question Enum Join answered by @saeedehp. I have one table full of enum value that is joined together to build some user role column in another table.

Example

Table 1 Enum Table

Role       - RoleEnumValue
-------------------------
Student    - 1
Researcher - 2
Lecturer   - 4
Teacher    - 8

Table 2 User Table

UserName   - Roles
-------------------------

John       - 3 
Melvin     - 14 

Result

UserName  - UserRoles
--------------------------------
John      - Student, Researcher
Melvin    - Researcher, Lecturer, Teacher

This is the sql query for getting the result. However i need to write switch case for every rows in Table 1

SELECT  t.UserName
    , ISNULL(t.C1 + ', ', '') + ISNULL(t.C2, '') + ISNULL(', ' + t.C3, '') + 
      ISNULL(', ' + t.C3, '') AS [UserRoles]
FROM
(
    SELECT UserName,
        CASE WHEN (Roles & 1) <> 0  THEN 'Student' END AS C1,
         CASE WHEN (Roles & 2) <> 0  THEN 'Researcher' END AS C2,
         CASE WHEN (Roles & 4) <> 0  THEN 'Lecturer' END AS C3,
         CASE WHEN (Roles & 8) <> 0  THEN 'Teacher' END AS C3
         ,... -- if i have more rows
    FROM "Table 2"
) t

Now the problem is I have table 1 with about 14 to 20 roles and it's not productive to keep adding switch case.

What is the other way to extract out that multiple roles and get the result like above? Can point out which venn diagram this probably fall under?

(I'm using Microsoft SQL Server)

---

Update

I think the problem i want to solve is to find user role quickly without opening the application to check user one by one.

Great answer by @Gordon Linoff,

Another great answer by @MatBailie, It makes me realize the answer is quite simple.

The solution.

SELECT 
u.UserName, 
STRING_AGG(r.Role, ',') AS AssignedRoles
FROM "Table 2" u
LEFT JOIN "Table 1" r ON r.RoleEnumValue & u.Roles <> 0
GROUP BY u.UserName

Answer 1

Fix your data model! There are few situations where bit fiddling is going to be the best solution. Instead, create a reference table for roles:

create table roles (
    role_id int identity(1, 1) primary key,
    role_name varchar(255)
);

Then create a users table:

create table users (
    user_id int identity(1, 1), primary key,
    user_name varchar(255)
);

Then create a user_roles table:

create table user_roles (
    user_role_id int identity(1, 1) primary key,
    user_id int references users(user_id),
    role_id int references roles(role_id)
)

The query you want is then:

select u.user_name, string_agg(r.role_name, ',')
from users u join
     user_roles ur
     on ur.user_id = u.user_id join
     roles r
     on r.role_id = ur.role_id
group by u.user_name;

What are the issues with bit fiddling?

• It limits the number of roles that you can have.
• It is an optimization that is probably not necessary.
• Searching for a particular role cannot take advantage of indexes.

Yes, it does have certain advantages. However, SQL has quite powerful mechanisms for representing many-to-many relationships. If you don't want to use them, then you need a very explicit explanation of why they are not adequate.

Answer 2

Instead of hard-coding the roles, use a join.

SELECT
   *
FROM
   table2
LEFT JOIN
   table1
       ON table2.roles & table1.RoleEnumValue = 1

This will give each user's roles on a separate row per role.

I recommend you stop there, as that's the correct normalised data-structure suitable for SQL and relational databases.

You can, however, collapse the results to a single row per user, with all roles in a single string.

This is usually a bad idea, and Will make subsequent SQL much harder...

SELECT
   table2.UserName,
   STRING_AGG(table1.Role, ', ')   AS all_roles
FROM
   table2
LEFT JOIN
   table1
       ON table2.roles & table1.RoleEnumValue = 1
GROUP BY
   table2.UserName