CODEWITHSUNDEEP
mysqlsql
Kara
Kara

Reputation: 785

MySQL: Using Case statements

I have three tables called 'Employees', 'Managers', 'Supervisors'

Employees
ID    Name
 1     Bob
 2     Alex
 3     Joe
 4     Melissa
 5     Hannah

Managers
MID   Name  
1     Bob
3     Joe

Supervisor
 SID   Name
 3     Joe
 4     Melissa

I am trying to check whether if an employee is either a manager or a supervisor or both, and have a result table that looks like

ID   Manager   Supervisor
1    Manager   NULL
2    NULL      NULL
3    Manager   Supervisor
4    NULL      SUPERVISOR
5    NULL      NULL

I want to try using a case statement in order to determine whether a fellow Employee ID is either a manager and/or a supervisor. But i'm not sure how to do this.

I know that I have to do something like

SELECT e.id /*how do i select the last 2 columns bc they are new?*/
FROM Employees e, Managers m, Supervisor s
CASE WHEN e.id = m.mid THEN 'manager' /*i don't think this is the correct format*/
CASE WHEN e.id = s.sid THEN 'supervisor'
....

Upvotes: 0

Views: 43

Answers (2)

Edwin Krause
Edwin Krause

Reputation: 1806

Try this:

SELECT ID , (
 SELECT MID AS Manager
 FROM Managers WHERE MID = ID 
 LIMIT 1
) AS Manager, (
 SELECT SID AS Supervisor
 FROM Supervisors WHERE SID = ID
 LIMIT 1
) AS Supervisors
FROM Employees

This will display your result as follows

ID   Manager   Supervisor
1    1         NULL
2    NULL      NULL
3    3         3
4    NULL      4
5    NULL      NULL

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You want to use left join. Follow a simple rule: do not use commas in the from clause. Always use explicit join syntax. Ignore anyone or anything that tells you otherwise. Such advice is very out-of-date.

select e.id,
       (case when m.mid is not null then 'Manager' end) as Manager,
       (case when s.sid is not null then 'Supervisor' end) as Supervisor
from employees e left join
     managers m
     on e.id = m.mid left join
     supervisors s
     on e.id = s.sid;

Also note that your tables are not properly normalized. Because managers and supervisors are employees, you should only have the name column in the employees table. It should not be repeated in the other tables, unless the same person could have different names when they take on the different roles.

Upvotes: 2

Related Questions