Microsoft SMSS – Joining tables on t2.primary key = t1.foreign_key

I need to join two tables together to create a table with columns for employee id, employee name and their boss’ name.

The ‘hier’ table

The ’employees’ table

The query I wrote is almost working, putting an employee name in the right spot, but not the right employee:

SELECT em.emp_id, em.emp_name, em.emp_name AS boss_name
FROM employees em
LEFT JOIN hier h ON (h.boss_id = em.emp_name)

Which outputs:

I need to have each person’s boss to have the right name, and in the case of Big Boss, ‘N/A’. Like so:

Answer

You need a self join with Employee table

SELECT em.emp_id, em.emp_name, e1.emp_name AS boss_name
FROM employees em
LEFT JOIN employees em1 ON em.boss_id = em1.emp_id