CODEWITHSUNDEEP
mysqlgroup-bycase-when
Vishal Zanzrukia
Vishal Zanzrukia

Reputation: 4973

Mysql Query with group by and case when

Suppose I have three tables like this in MySQL DataBase.


1. User(userId,name)
2. Role(roleId,role)
3. UserRoleMap(userRoleId,userId,roleId)

One User may have multiple roles.
In case when user have more than one role, I have to choose one role which comes first in hierarchy(Pre-defined). Order for hierarchy is 1.Admin,2.Supervisor,3.User.
For example If John have two roles(Admin,User) then my expected output is : 

+----------+-----------+
|John      |Admin      | (John have two roles:User,Admin)
|Vishal    |Supervisor | (Vishal have two roles:User,Supervisor)
in final result of query.

I tried following query.


select user.name,
FIND_IN_SET(trim(role.role),'Admin,Supervisor,User') as 'roleIndex',
(case when(FIND_IN_SET(trim(role.role),'Admin,Supervisor,User')=1) then 
                'Admin'
       else
         case when(FIND_IN_SET(trim(role.role),'Admin,Supervisor,User')=2) then
                'Supervisor'
         else 
            case when(FIND_IN_SET(trim(role.role),'Admin,Supervisor,User')=3) then
                    'User'
            end
         end
      end) as role
from
User user 
inner join UserRoleMap userRole on user.userId = userRole.userId
inner join Role role on role.roleId = userRole.roleId
group by user.userId;


Thanks. Every answer would be appreciate.

Upvotes: 0

Views: 1745

Answers (3)

Shafeeque
Shafeeque

Reputation: 2069

I am not sure about my answer. Anyway I hope this will help you

SELECT A.name, C.role
FROM `user` A
JOIN userrolemap B ON A.`userId` = B.`userId`
JOIN role C ON B.roleId = C.roleId
ORDER BY C.roleId, A.name

EDIT

You can specify order using field as

SELECT A.name, C.role
FROM `user` A
JOIN userrolemap B ON A.`userId` = B.`userId`
JOIN role C ON B.roleId = C.roleId
ORDER BY FIELD( C.role, 3, 1, 2 ),A.name

EDIT

SELECT A.name, C.role
FROM `user` A
JOIN userrolemap B ON A.`userId` = B.`userId`
JOIN role C ON B.roleId = C.roleId
GROUP BY A.userId
ORDER BY FIELD( C.role, 3, 1, 2 ) , A.name

Upvotes: 2

Vishal Zanzrukia
Vishal Zanzrukia

Reputation: 4973

finally I got solution 

select user.name, 
case when (GROUP_CONCAT(role.role) like '%Admin%') then 
        'Admin'
    else
        case when (GROUP_CONCAT(role.role) like '%Supervisor%') then 
                'Supervisor'
            else
                case when (GROUP_CONCAT(role.role) like '%User%') then 
                        'User'
                end
          end
end as 'role'
from User user, Role role, UserRoleMap userRole where
user.userId = userRole.userId and
role.roleId = userRole.roleId
group by user.userId;

Upvotes: 0

Yoshi-
Yoshi-

Reputation: 71

Instead of directly saving the role, you should rather go for a different approach.

You assign to each role a power of 2. EG: User - 1 Admin - 2 Supervisor 4

If a user has multiple roles you just add them up. So when someone is both a User and Supervisor you would save "5" in the Database.

This makes your query much easier, because now you can do something like this:

CASE
    WHEN role >= 1 THEN 'User'
    WHEN role >= 2 THEN 'Admin'
    WHEN role >= 4 THEN 'Supervisor'
END

Upvotes: 0

Related Questions