Problem filtering information in MySQL query I've a little problem with a SQL query.

The objective is to get an employee profile related to a role.

Table EMPLOYEE (Louis,Mary,Christian):


EmpId
EmpName


Table ROLE (Chef,Waiter,NULL):


RoleID
RoleNAME


Table EMPROLE:


EmpId
RoleId


An employee should have 0..n roles. To get an employee profile:


IF Chief -> BOS
ElseIF waiter -> CHA
Else -> CUS


The objective is to print EmpName, all roles, and the profile.

I can print all roles with group_concat and get the role using case but that only works if the role is the first occurrence in the group_concat column.

My query:

select EMPLOYEE.EmpName,GROUP_CONCAT(ROLE.RoleName) as role_, 
(CASE WHEN ROLE.RoleName = "Chief" THEN "BOS" 
CASE WHEN ROLE.RoleName = "waiter" THEN "CHA" 
ELSE THEN "NULL" 
END) AS PROFILE 
FROM EMPLOYEE,ROLE,EMPROLE 
WHERE EMPLOYEE.EMPLOYEEID=EMPROLE.EMPLOYEEID AND EMPROLE.ROLEID=ROLE.ROLEID group by EMPLOYEE.EmpName;


Output:

+----------+--------------------------------+--------+
| EmpName  | roles                          | PERFIL |
+----------+--------------------------------+--------+
| Louis    | Waiter,Chief                   | NULL   |
| Mary     | Chief,Waiter                   | BOS    |
| Christian| NULL                           | NULL   |
+----------+--------------------------------+--------+


How can I get the information right?
This might be closer:

select  e.EmpName,
        GROUP_CONCAT(r.RoleName) AS roles
        CASE
            WHEN FIND_IN_SET("Chief",   GROUP_CONCAT(r.RoleName)) THEN "BOS"
            WHEN FIND_IN_SET("waiter ", GROUP_CONCAT(r.RoleName)) THEN "CHA"
            ELSE                                                       "CUS"
        END  AS Profile
    FROM  EMPLOYEE AS e
    JOIN  EMPROLE  AS er  ON e.EMPLOYEEID = er.EMPLOYEEID
    JOIN  ROLE     AS r   ON er.ROLEID = r.ROLEID
    group by  e.EMPLOYEEID;


Notes:


Please use the JOIN ON syntax instead of commajoin and WHERE.
The GROUP_CONCAT occurs 3 times; that can be avoided with the expense of a nested query.  I did not think it was worth it.
Be sure to have something like this on EMPROLE:

PRIMARY KEY(employeeid, roleid),
INDEX(roleid, employeeid)