0

I have 3 tables, Name, Supervisor and Manager. I need a query that displays a list of names and whether they are supervisors, managers, or both.

My current query accomplished 90% of the problem, returns all those that are supervisors, and checks if they are also manager or not. However, if the user is not a "Supervisor" but is a "Manager" than he is not listed on the query. 

SELECT name.id,
name.email,
IF(sup.code = 1, 'Yes', 'No') as Supervisor,
IF(man.userId IS NOT NULL, 'Yes', 'No') as Manager
FROM employee name
LEFT JOIN supervisor sup ON name.id = sup.id
LEFT JOIN manager man ON name.id = man.id
WHERE sup.code = 1

Thank you

rafacardosoc
  • 251
  • 1
  • 7
  • 16

1 Answers1

2

Your problem is the where clause. By including that, you are limiting your results to only include those that have that value set. Obviously this wont be true for those who are not supervisors.

This breaks the outernish of your join. To have additional conditions on a left join, without breaking that functionality, you need to move the criteria to the on clause. In this case, it looks like you could also just remove the where clause entirely, unless there is possibly more than one entry in the supervisor table for each employee.

Anyway, your two choices are:

SELECT name.id,
name.email,
IF(sup.code = 1, 'Yes', 'No') as Supervisor,
IF(man.userId IS NOT NULL, 'Yes', 'No') as Manager
FROM employee name
LEFT JOIN supervisor sup ON name.id = sup.id
LEFT JOIN manager man ON name.id = man.id

And:

SELECT name.id,
name.email,
IF(sup.code = 1, 'Yes', 'No') as Supervisor,
IF(man.userId IS NOT NULL, 'Yes', 'No') as Manager
FROM employee name
LEFT JOIN supervisor sup ON name.id = sup.id AND sup.code = 1
LEFT JOIN manager man ON name.id = man.id
pala_
  • 8,901
  • 1
  • 15
  • 32
  • Thank you @pala_ I modified your answer (adding the AND condition to the Join) and maitained the WHERE clause so that way i can filter out records that are "No" for both sup and manager, which i am not interested in seeing. – rafacardosoc Jun 02 '15 at 13:33