Select records in Mysql if the other column is set to none-CodePudding

how can I get all the departments where is a ID is equal to manager column or if the manager is set to NULL it is equal to the supervisor.

table_department:

dept_id, dept_name, manager_id, supervisor_id

1     IT-admin           1            NULL
2     IT-hardware       NULL           1
3     IT-system          4             1
4     Engineering        3            NULL

table_users:

user_id, username 
1       username1
2       username2
3       username3
4       username4
5       username5

If in my condition the user is username1 and his id is 1, How to query and show IT-admin and IT-hardware department because username1 is the manager/supervisor of that departments.

Any help would be appreciated, Thank you.

CodePudding user response：

Try this:

SELECT dept_id, dept_name, user_id, username
FROM table_department 
INNER JOIN table_user ON COALESCE(manager_id, supervisor_id) = user_id;

CodePudding user response：

I suggest aggregating by the coalesced value of the manager/supervisor ID value. Then, use conditional aggregation to pivot out the correct department. For users who are supervisors, choose the supervisor department, otherwise use the manager department.

SELECT COALESCE(manager_id, supervisor_id) AS user_id,
       CASE WHEN COUNT(CASE WHEN supervisor_id IS NOT NULL THEN 1 END) > 0
            THEN MAX(CASE WHEN supervisor_id IS NOT NULL THEN dept_name END)
            ELSE MAX(CASE WHEN manager_id IS NOT NULL THEN dept_name END)
       END AS dept_name
FROM table_department
GROUP BY 1;

Demo

By the way, it would probably help to better normalize your table design, and get a single column per user with possible role values. This would avoid the ugly CASE expressions in my answer.

CodePudding user response：

You can either use OR in you query.

select * from table_department d 
where exists (select 1 from table_users u 
              where u.username = 'username1'
              and (u.id = d.manager_id or (d.manager_id is NULL and u.id = d.supervisor_id)))