Generate sql report with tinyint field used to signify primary role or subrole?-CodePudding

Im trying to create sql for dataset that will pull user_name, role, and subrole. users can have multiple roles or subroles assigned. What i want to do is have the query return the values where its a primary role as ROLE and the ones where its not primary as Secondary Role. Just testing out with single user as example

SELECT DISTINCT
  CONCAT(users.first_name, ' ', users.last_name) AS 'User Name',
  GROUP_CONCAT(roles.role_name) AS 'Role',
  CONCAT(managers.first_name, ' ', managers.last_name) AS 'Manager',
  roles.primary
FROM users
  LEFT OUTER JOIN user_roles
    ON users.id = user_roles.user_id
  LEFT OUTER JOIN user_managers
    ON user_managers.user_id = users.id
  LEFT OUTER JOIN roles
    ON user_roles.role_id = roles.id
  LEFT OUTER JOIN users managers
    ON user_managers.manager_id = managers.id
WHERE users.first_name LIKE"Dave%"
GROUP BY primary

This returns as example

User Name   Role                                            Manager         primary
Dave Jones  HR                                          Susan Smith         0
Dave Jones  Operations Manager,Administrator,Mail Room  Susan Smith         1

What i want to do is have the roles that have primary value of 1 show under field Role and want to have Subrole field that will showroles with primary value of 0 so result would look like

User Name   Role                                        Subrole       Manager       
Dave Jones  Operations Manager,Administrator,Mail Room    HR          Susan Smith

wont need to include that primary value field in the output. Any ideas how best to do this ? Thanks for any help

CodePudding user response：

You can put a conditional expression in GROUP_CONCAT(). So the concatenation for the primary roles will only return the primary roles, while the concatenation for subroles should only return the secondary roles.

SELECT DISTINCT
  CONCAT(users.first_name, ' ', users.last_name) AS 'User Name',
  GROUP_CONCAT(CASE roles.primary WHEN 1 THEN roles.role_name END)) AS 'Role',
  CONCAT(managers.first_name, ' ', managers.last_name) AS 'Manager',
  GROUP_CONCAT(CASE roles.primary WHEN 0 THEN roles.role_name END)) AS 'SubRole'
FROM users
  LEFT OUTER JOIN user_roles
    ON users.id = user_roles.user_id
  LEFT OUTER JOIN user_managers
    ON user_managers.user_id = users.id
  LEFT OUTER JOIN roles
    ON user_roles.role_id = roles.id
  LEFT OUTER JOIN users managers
    ON user_managers.manager_id = managers.id
GROUP BY users.id