Why is a row missing?

Question

I have small hierarchy table.

        NR  ENAME       BOSS 
       | 1| Johnson   | NULL|
       | 2| Jackson   | 1   |
       | 3| Wright    | 1   |
       | 4| Carell    | 3   |
       | 5| Statham   | 3   |
       | 6| Lincoln   | 4   |

Now I'd like to see every Employee and their Boss.

SELECT Employee.ENAME Employee, BOSS.ENAME Boss
FROM Employee
JOIN Employee AS Boss ON Employee.BOSS=Boss.NR

By running this I get every employee except Johnson. What do I need to do to get a row with Employee Johnson and Boss NULL?

possible duplicate of [What is SELF JOIN and when would you use it?](http://stackoverflow.com/questions/3362038/what-is-self-join-and-when-would-you-use-it) — dcaswell, Sep 07 '13 at 18:46
Always a great reference http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg — Jacob Goulden, Sep 07 '13 at 18:50

score 1 · Accepted Answer · answered Sep 07 '13 at 18:44

1

You are doing a SELF JOIN on your table and your BOSS column has NULL value against Johnson. You need to do it like this:-

SELECT Employee.ENAME Employee, BOSS.ENAME Boss
FROM Employee
LEFT OUTER JOIN Employee AS Boss ON Employee.BOSS=Boss.NR

answered Sep 07 '13 at 18:44

Rahul Tripathi

168,305
31
280
331

Why is a row missing?

1 Answers1