Nested Select Select

Question

This is an offshoot of a previous question -- I have code that looks like this

select  EMPLOYEE_NAME,
        HIRE_DATE,
        IFNULL(SALARY,0),
        manager_id, 
       (select ifnull(employee_name,"unkown") from employee b where b.employee_id = a.manager_id)

from employee a
order by a.EMPLOYEE_NAME;

I want the word "unkown" to appear if there is no manager value.

Basically its a large table with employee IDs and Manager IDs. (and the manager IDs are equivalent to the employee IDs) so i can get the list of those that join correctly but the blanks come out blank ... please advise.

Do you just want a list of employees with their manager names? — PM 77-1, Jul 24 '14 at 20:35

score 0 · Answer 1 · answered Jul 24 '14 at 20:34

0

select  EMPLOYEE_NAME,
        HIRE_DATE,
        coalesce(SALARY,0),
        manager_id, 
        coalesce(b.employee_name,'unknown') as manager_name

from employee a
left join employee b on b.employee_id = a.manager_id
order by a.EMPLOYEE_NAME;

answered Jul 24 '14 at 20:34

Andrew

26,629
5
63
86

I had to change double quotes to single quotes on unknown but it worked. What exactly is coalesce? – YelizavetaYR Jul 24 '14 at 20:38
Corrected the quotes - Coalesce is the ANSI equivalent of ifnull / isnull / nvl that each RDBMs has. Coalesce exists in all of them so its just more transferrable. – Andrew Jul 24 '14 at 20:39
Coalesce is not the same as IsNull, NVL, etc... See: http://stackoverflow.com/questions/7408893/using-isnull-vs-using-coalesce-for-checking-a-specific-condtion – Chris Fremgen Jul 24 '14 at 20:45
Yes their are some more complex use-case differences between it and each of the RDBMs implementations - many of which are on edge cases - none of which appear in this simple question. Try select nvl(1,1/0) from dual vs select coalesce(1,1/0) from dual - some of the differences can be beneficial. – Andrew Jul 24 '14 at 20:51

score 0 · Answer 2 · edited Jul 24 '14 at 20:42

0

Use a outer join to lookup the managers name.

select  EMPLOYEE_NAME,
        HIRE_DATE,
        IFNULL(SALARY,0),
        IFNULL(b.EMPLOYEE_NAME, 'unknown') as 'MANAGER_NAME' 

from employee a
left outer join employee b on a.manager_id = b.employee_id
order by a.EMPLOYEE_NAME;

edited Jul 24 '14 at 20:42

Andrew

26,629
5
63
86

answered Jul 24 '14 at 20:38

Rusty1

334
2
6

This works too. I had to only adjust the alias to the select statements (as i had to do to the solution above). and it worked. thank you – YelizavetaYR Jul 24 '14 at 20:41
thanks Andrew need to brush up my formatting skills ;) – Rusty1 Jul 24 '14 at 20:45

Nested Select Select

2 Answers2