2

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?

  • What did you want for PROFILE? Perhaps: BOS, BOS, NULL? – Rick James Sep 5 at 20:47
1

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)
    
  • Thank you very much Rick, it works! – m0rgul Sep 9 at 11:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.