Take the result of query to new table and then make query from it in SQL server

Question

_______________table employee________
|emp_id|first_name|last_name|super_id|
|100   |  David   |Wallace  | NULL   |
|101   |  Jan     |Levinson | 100    |
|102   |  Michael |Scott    |  100   |
|103   |  Angerla |Martin   | 102    |
|104   |  Kelly   |Kapoor   | 102    |
|105   |  Stanley |Hudson   | 102    |
|106   |  Josh    |Poster   | 100    |
|107   |  Andy    |Bernard  | 106    |
|108   |  Jim     |Halper   | 106    |
--------------------------------------

super_id stand for supervisor id is foreign key references emp_id. I want to make a query to get results like:

____________________________________________________________
|emp_id|first_name|last_name|super_firstName|super_lastName|
|100   |  David   |Wallace  | NULL          | Null         |
|101   |  Jan     |Levinson | David         | Wallace      |
|102   |  Michael |Scott    | David         | Wallace      |
|103   |  Angerla |Martin   |  Michael      |Scott         |
|104   |  Kelly   |Kapoor   |  Michael      |Scott         |
|105   |  Stanley |Hudson   |  Michael      |Scott         |
|106   |  Josh    |Poster   | David         | Wallace      |
|107   |  Andy    |Bernard  | Josh          | Bernard      |
|108   |  Jim     |Halper   | Josh          | Bernard      |
------------------------------------------------------------

my query

select employee.first_name,employee.last_name from employee join
(select emp_id,first_name ,last_name from employee where emp_id in (select super_id from employee)) as a
on employee.super_id=a.emp_id

what is the problem with my code?

Answer 1

You will be having 1 is to many super_id, so we need to get the distinct first before joining it to your employee table.

select t1.emp_id, t1.first_name,t1.last_name, t2.first_name, t2.last_name 
from employee t1
left join 
    (select row_number() over (partition by super_id order by super_id) rn
        , super_id, emp_id
        , first_name, last_name from employee) t2 on t2.emp_id = t1.super_id
where t2.rn = 1

Answer 2

A self join should work here:

SELECT
    e1.emp_id,
    e1.first_name,
    e1.last_name,
    e2.first_name AS super_firstName,
    e2.last_name AS super_lastName
FROM employee e1
LEFT JOIN employee e2
    ON e1.super_id = e2.emp_id
ORDER BY
    e1.emp_id;

Demo