im new to sql and i have the following problem. I have this kind of table with employee number, surname, job and a column named 'dir' that shows the employee number of the director of the employee in that row.
I was trying to do it using the decode function with some subqueries but failed :(. Is there any way to do it with decode? I have not learned yet more advanced functions.
| emp_no | Surname | Job | dir |
|---|---|---|---|
| 1 | a | director | |
| 2 | b | analist | 1 |
| 3 | c | director | |
| 4 | d | manager | 1 |
| 5 | e | analist | 3 |
| -------- | -------- | -------- | -------- |
I need to write a query that will show in the first column, every employee's name and, in the second column, the name of that employee's director, or in case he has no director the legend "has no director". Example:
| Surname | director |
|---|---|
| a | has no director |
| b | a |
| c | has no director |
| d | a |
| e | c |
Thanks in advance everyone!!
CodePudding user response：
select a.Surname,
case when a.dir is null then 'has no director'
else b.Surname end as director
from employee a
left join employee b on a.dir=b.emp_no
order by Surname;
You can see the example here ;