1

I am trying to learn oracle sql and below is my query. I wanted to print everyone's manager and if someone doesn't a manager then it should return "No One".

Problem: I am getting a blank statement instead of "No One". Any help?

SELECT NVL(m.first_name || ' '
    || m.last_name, 'No One') || ' supervises '
    || w.first_name || ' ' || w.last_name
  FROM employees w, employees m
 WHERE w.manager_id = m.employee_id(+);

NVL(M.FIRST_NAME||''||M.LAST_NAME,'NOONE')||'SUPERVISE
------------------------------------------------------
James Smith supervises Ron Johnson
Ron Johnson supervises Susan Jones
Ron Johnson supervises Fred Hobbs
  supervises James Smith
diziaq
  • 6,881
  • 16
  • 54
  • 96
noman pouigt
  • 906
  • 11
  • 25

2 Answers2

1

I believe the accepted answer can be simplified by recognizing that when an employee be unsupervised, there will be no matching record for his manager. As a result, both the manager's first and last name will either be NULL or not NULL together.

SELECT NVL(m.first_name, 'No One', m.first_name || ' ' || m.last_name)
    || ' supervises ' || w.first_name || ' ' || w.last_name
FROM employees w LEFT JOIN employees m
    ON w.manager_id = m.employee_id

Update:

The (+) operator is Oracle's syntax for a LEFT JOIN. Have a look at this Stack Overflow article to learn more about Oracle's (+) operator.

Community
  • 1
  • 1
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
1

This is because of the reason, that when m.first_name and m.last_name will be null for the manager, then m.first_name || ' ' || m.last_name will not result in null, but with ' '. So, it will print space instead of 'No One'. You can achieve your desired results by using nvl2 something like 

SELECT NVL2( m.first_name ||m.last_name , m.first_name || ' ' || m.last_name, 'No One' )
   || ' supervises '
   || w.first_name
   || ' '
   || w.last_name FROM employees w, employees m WHERE w.manager_id = m.employee_id(+);
Muhammad Zeeshan Karamat
  • 326
  • 3
  • 12
  • I will mark your answer correct. Just wondering what does (+) in this query (WHERE w.manager_id = m.employee_id(+)) stand for? – noman pouigt Feb 08 '16 at 05:54
  • @TimBiegeleisen: yes as ideally I would have marked your answer right but I am a complete newbie and this answer is what I understood well. I will upvote your answer though. Just wondering what (+) does in my query. – noman pouigt Feb 08 '16 at 05:58
  • This is oracle specific way for left outer join http://stackoverflow.com/a/4020794/2594724 – Muhammad Zeeshan Karamat Feb 08 '16 at 06:25