CODEWITHSUNDEEP
mysqljoin
rafacardosoc
rafacardosoc

Reputation: 251

MySQL Join Wrong Result

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

Upvotes: 0

Views: 72

Answers (1)

pala_
pala_

Reputation: 9010

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

Upvotes: 2

Related Questions