SQL self join problem with null values

Lets take the classic self join example of Employee table where we have the columns empId开发者_如何学运维,managerId,empName,Managername. I am trying to see that for each employee who is the manager and then joining the manager name with another name in table Name. Now the part I am having problems with is that the manager id can be null for some records. In those cases, the sql doesn't work since mgr.name is null for the row which had the mgrId as null :

SELECT emp.Name, mgr.Name FROM Employee e
LEFT JOIN Employee mgr ON e.empId=mgr.mgrId 
JOIN Name nm ON nm.name = mgr.Name

Can someone please provide a solution for this?

Sorry for oversimplifying the problem: Its more like for each employee row, I also want the mgr row(the row where mgrId is the empId) and then join the attributes of mgr row to other tables. Something like this:

select 
   emp.empId,mgr.empId,dept.deptName 
from Employee emp  
JOIN Address addr on 
   emp.houseNo = addr.houseNo  
JOIN dept dept on 
   dept.deptAddress = addr.deptAddress  
LEFT JOIN Employee mgr on 
   emp.empId = mgr.empId  
JOIN Address address on 
   mgr.houseNo = address.houseNo  
JOIN dept department on 
   department.houseNo=address.deptAddress  
where 
   department.deptId=dept.deptId  

Using all left joins didn't work for this. Thanks for the help.

Once you introduce a left join, you generally want to follow all subsequent joins with LEFT too. A single INNER JOIN in the chain reduces everything before it to an INNER JOIN as well.

SELECT 
    emp.Name, mgr.Name 
FROM Employee e
LEFT JOIN Employee mgr ON 
    e.empId = mgr.mgrId 
LEFT JOIN Name nm ON 
    nm.name = mgr.Name

First, don't you have the join backwords? Shouldn't

         ON e.empId = mgr.mgrId 

be

         ON e.mgrId = mgr.empId

Even if yours works the table named "mgr" should hold the record with mgr.empId and mgr.name so it is clear.

Second, I'm not liking the joining on Name part. You should convert this to IDs. (Someone gets married and the next thing you know you have a bunch of orphans....) I suspect this table isn't really needed.

Third, the fact that 2 columns in the result set have the same name (Name) could lead to problems later - bad practice if nothing else.

This is the SQL I would use:

SELECT emp.Name, '(none)' AS Manager 
FROM Employee emp
WHERE NOT EXISTS (SELECT 1
    FROM Employee mgr 
    WHERE mgr.empId = emp.mgrId)

UNION ALL

SELECT emp.Name, mgr.Name AS Manager 
FROM Employee emp
JOIN Employee mgr ON emp.mgrId=mgr.empId 
JOIN Name nm ON nm.name = mgr.Name
ORDER BY 1, 2

Don't worry if this looks inefficient, the modern SQL compiler ends up with the same thing in most cases no matter how you write it.

But first, I would run this SQL to see if I had any employees that didn't have a record in the Name table and fix them:

SELECT *
FROM Employee e
LEFT JOIN Name nm 
ON nm.name = e.Name
WHERE nm.name is null

Just move the ON clause to where it is more appropriate

SELECT emp.Name, mgr.Name
FROM Employee e
    LEFT JOIN Employee mgr 
         JOIN Name nm
         ON nm.name = mgr.Name
    ON e.empId=mgr.mgrId